- #!/bin/sh
- oraclehome=$ORACLE_HOME
- echo $oraclehome
- localdir="/oracle/data"
- echo $localdir
- #删除已经存在的临时dmp文件
- rm -rf $localdir/$2temp.dmp
- rmresult=$?
- echo "rm $2temp.dmp result:$rmresult"
- #将用户$1的表空间导出
- su - oracle -c "exp dba/dba file=$localdir/$2temp.dmp owner=$1"
- expresult=$?
- if [ "$expresult" != "0" ];then
- echo "exp $1 tablespace failure!!!"
- fi
- #先删除用户$2及其表空间,然后再新建该用户及表空间
- su - oracle -c "${ORACLE_HOME}/bin/sqlplus /nolog" <<EOF
- connect / as sysdba
- drop user $2 cascade;
- drop tablespace $2 including contents and datafiles;
- create tablespace $2 datafile '/oracle/product/10.2.0/oradata/$2.dbf' size 5M autoextend on;
- create user $2 identified by "$2" default tablespace $2 temporary tablespace TEMP profile DEFAULT;
- grant connect to $2;
- grant resource to $2;
- grant create any table to $2;
- grant create any trigger to $2;
- grant create any type to $2;
- grant create any view to $2;
- grant unlimited tablespace to $2;
- exit
- EOF
- crdrresult=$?
- if [ "$crdrresult" != "0" ];then
- echo "drop user and tablespace failure!!!"
- echo "create user and tablespace failure!!!"
- else
- #刚建完的用户不能马上使用,等候10秒
- sleep 10s
- #更换dmp文件中的表空间名
- sed -i 's/TABLESPACE "$1"/TABLESPACE "$2"/g' $localdir/$2temp.dmp
- #使用imp命令导出表空间数据到用户$2的表空间
- su - oracle -c "imp dba/dba file=$localdir/$2temp.dmp fromuser=$1 touser=$2"
- impresult=$?
- if [ "$impresult" != "0" ];then
- echo "imp failure!!!"
- else
- echo "imp success!!!"
- fi
- fi
- #该片段来自于http://www.codesnippet.cn/detail/121120137084.html
来源: http://www.codesnippet.cn/detail/121120137084.html