Tuesday, April 8, 2008

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

Problem Description:

SQL> drop user strmadmin cascade;
drop user strmadmin 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 = 'STRMADMIN';

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 strmadmin 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 strmadmin cascade;
User dropped.

Related Documents
Drop User in Oracle

4 comments:

Martijn Hinten said...

You might consider to lock the account before killing its sessions in order to prevent the account to log in again, just after you killed its sessions but before issueing the drop user command. This is especially nessecary in environments where batch jobs, application servers etc automatically establish sessions.

My 2 cents.

Arju said...

Good thinking. I appreciate it.

Jerome Christopher said...

Locking the user account before removing the sessions proved to be extremely useful in a refresh process.Thanks.

Jay said...

It worked for me. Thanks.