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

Recently when i was trying to drop one of the schema then the drop command was failing with below error:

SQL> drop user expDbfm cascade;
drop user expDbfm cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

The Error message itself is very clear the the user is connected so it cannot be dropped. Now we must find out the session for this specific users and kill those sessions in order to drop this user.

Find out the session for the connected user

SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'FINTEST5';

SID         SERIAL#              USERNAME            LOGON_TIME   Days Inactive
---------- ---------- ------------------------------ ----------   -------------
3             28047             expDbfm              22-Dec-17    .001597222

Kill all listed sessions

SQL> ALTER SYSTEM KILL SESSION '3,28047';

System altered.

SQL>

Check any active session again before dropping the user

SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'expDbfm';

no rows selected

>> Now we are read to drop the user

SQL> drop user expDbfm cascade;

User dropped.

SQL>

This can be done using TOAD or Oracle enterprise manager  as well. We need to check all connected users for the user which we want to drop and then kill all sessions belonging to that users.

Hits: 3

Leave a Reply

Your email address will not be published. Required fields are marked *