ORA-01940: Cannot Drop A User that is Currently Connected

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.