背景:
在我们工作的时候,很多同事都会直接用PLSQL Developer连接Oracle数据库,而我们在重新导入数据库备份文件时,需要把当前用户的所有连接的session,kill掉,才能将该用户的数据,全部删除掉,之前在用plsql developer将所有的session手动kill掉,然后再执行drop user XXX cascade,就在你把session kill掉,与drop user 这一瞬间,可能又有很多同事,连接上oracle,因为又有当前连接,这样在执行drop user 时会失败。
用法:将a.dmp文件导入到hisuser_damon用户下
53数据库:
1.将要导入的数据库文件a.dmp copy到C:\app\Administrator\admin\orcl\dpdump目录下
2.运行cmd 执行以下命令
cd c:\shareFolder\damon
python killSession.py hisuser_damon a.dmp
55数据库:
1.用putty或者cygwin,oracle用户登录192.168.208.55
3.将a.dmp文件copy到/home/oracle/app/oracle/admin/orcl/dpdump目录下
2.执行以下命令
cd /home/oracle/bin
./killSession.py hisuser_damon a.dmp
脚本源码:
#FileName:killSession.py
import cx_Oracle,re,os,sys
# check the number of argvs
if len(sys.argv) < 3:
print('please input username and dumpfile name')
sys.exit()
# connect oracle
con = cx_Oracle.connect('sys','Aluhisno1','192.168.208.53:1521/orcl',cx_Oracle.SYSDBA)
cur = con.cursor()
userName = sys.argv[1].upper()
# check if oracle user exists
cur.execute("select count(1) from dba_users where username='"+userName+"' ")
cur_sid = con.cursor()
cur_temp = con.cursor()
for result in cur:
if result[0] == 1:
cur_sid.execute("select sid,serial# from v$session where username='"+userName+"' ")
for result in cur_sid:
sqlstr='alter system kill session '+'\''+str(result[0])+','+str(result[1])+'\''
cur_temp.execute(sqlstr)
print("kill session successfully")
# drop user
cur_temp.execute("drop user "+userName+" cascade")
print("drop oracle user:"+userName+" successfully")
else:
print("There is not a user named "+ userName + " before")
# create user
cur.execute("create user "+userName +" identified by Aluhisno1 DEFAULT TABLESPACE HISSPACE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ")
cur.execute("grant connect,dba to "+userName)
cur.close()
cur_sid.close()
cur_temp.close()
con.close()
imp_cmd = 'impdp '+ userName + '/Aluhisno1 dumpfile='+sys.argv[2]+' remap_schema=hisuser2:'+userName
os.system(imp_cmd)