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


SQL> drop user DCS_LIVE cascade;
drop user DCS_LIVE 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 = 'DCS_LIVE';
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 DCS_LIVE 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 DCS_LIVE cascade;
User dropped.
Advertisements

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: