ORA-01940: Cannot Drop A User that is Currently Connected
While dropping a user in oracle database , you may face below error. ORA-01940
Problem:
SQL> drop user SCOTT cascade
2 /
drop user SCOTT cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
Solution:
1. Find the sessions running from this userid:
SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';
SID SERIAL# STATUS
---------- ---------- --------
44 56381 INACTIVE
323 22973 INACTIVE
2. Kill the sessions:
SQL> ALTER SYSTEM KILL SESSION '44,56381' immediate;
System altered.
SQL> ALTER SYSTEM KILL SESSION '323,22973' immediate;
System altered.
SQL> SELECT SID,SERIAL#,STATUS from v$session where username='SCOTT';
no rows selected
3. Now the drop the user:
SQL> drop user SCOTT cascade
user dropped.
No comments:
Post a Comment