删除oracle用户报ORA-01940: cannot drop a user that is current

删除数据库用户的时候经常会遇到这样的错误;

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#
---------- ----------
137 41813
335 25993
464 58747
500 9153
531 50402
629 9264
631 11237
664 36180


8 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;

相关推荐