shell脚本操作oracle删除表空间、创建表空间、删除用户
oracle下表空间的导出,用户的删除,表空间删除,用户新建,表空间新建,数据导入的shell
复制代码代码如下:
#!/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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |