ORA-01940: Cannot drop user that is currently connected

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

Solution 

Make sure the user is logged out, then re-execute the command.

If you are permitted to kill the session of that user then find out the connected user sid and serial# by,
SQL> select sid, serial# from v$session where username = ‘USERNAME’;

 SID    SERIAL#
———- ———-
 268       1268
 315       1223
If RAC use GV$SESSION view to get instance#
NOTE  Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop users who automatically establish session like to drop an application user or to drop a user who performs batch jobs.

SQL> Alter user username account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;           (use @instance# if RAC db)
       System altered

SQL> alter system kill session ‘315,1223’;
      System altered

And then drop the user.

SQL> drop user username cascade;
 User dropped
 
Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: