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: [email protected]

Sina: weibo.com/kaijunfeng

Yahoo: [email protected]