删除数据库用户的时候经常会遇到这样的错误;
ORA-01940: cannot drop a user that is currently connected
原因是有程序在连接我们需要删除的用户,我们删除用户之前一定要先把连接断掉;
1、查找帐号下哪些连接在运行:
SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USERNAME';
注意:此处需要大写
SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='SSTEST';
SID SERIAL#
---------- ----------
41813
25993
58747
9153
50402
9264
11237
36180
rows selected.
2、删除用户进程;
SQL>ALTER SYSTEM KILL SESSION '137,41813';
System altered.
SQL>ALTER SYSTEM KILL SESSION '33525993;
System altered.
3、重新查看用户连接,并确认无连接在使用
SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='SSCRM';
4、删除用户
drop user sstest cascade ;
-------------------------------------------------------------------------------------------------------------------------------------------
还有另外一种很暴力的方式:
1.将lsnrctl监听程序关闭,不允许连接再进来
2.关闭数据库并启动shutdown immediate --------> startup
3.此时删除用户