ORA-01940:cannot drop a user that is currently connected

故障原因：
今天搭建一个数据库测试环境，发现数据库已存在一个用户，现在想删除这个用户，在执行 “drop user user_name cascade;”提示ORA-01940

故障分析：
使用cascade参数删除用户时显示“cannot drop a user that is currently connected”，说明当前用户可能在使用，需要查看一下相应的会话，如果有活动的把它关闭掉。

处理过程：

SQL> select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = '<your user>'and p.addr (+) = s.paddr;

SQL> select job from dba_jobs where log_user='<your user>';

SQL> select queue_table,qid from dba_queues where wner='myuser';

SQL> select apply_name from dba_apply where queue_owner='myuser';

SQL> select capture_name, queue_name, from dba_capture where queue_owner='myuser';

SQL> select propagation_name from dba_propagation where source_queue_owner='myuser' or destination_queue_owner='myuser';

 

Kill the session that has not been already kill with:

alter system kill session '<sid>,<serial#>';

 

If all the session are noted 'KILLED' then kill the Unix process (column

spid) with "kill -9".

Then after a while the session will be removed from v$session and you will

be able to drop the user.

=========================================================================================

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任! 谢谢合作！

QQ: 164798858@qq.com
Sina: weibo.com/kaijunfeng
Yahoo: fffygapl@yahoo.com.cn