Friday 21 June 2013

FIX-ORA-01940:Cannot drop a user that is currently connected.


SQL> drop user manju cascade;

*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution of The Problem:

Find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'manju';

   SID    SERIAL#
---------- ----------
   268       1268
   315       1223

Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs.

SQL> Alter user manju account lock;

Now kill the connected session.
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';

System altered.

And then drop the user.
SQL> drop user manju cascade;
User dropped.