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

You can get “ORA-01940: cannot drop a user that is currently connected” error during the drop any user.

 

Details of error are as follows.

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

 

To solve this problem, you need to lock related user firstly.

SQL> alter USER MSD account lock;

User altered.

 

Then kill the session of related user as follows.

SQL> select s.sid, s.serial#, s.status, p.spid from gv$session s, gv$process p where s.username = 'MSD' and p.addr (+) = s.paddr;
SID SERIAL# STATUS SPID
——— ———- ——– ————
163 34 INACTIVE 342463
634 63 INACTIVE 153783

 

 

 

Kill these sessions as follows.

SQL> alter system kill session '163,263,@1';
System altered.

SQL> alter system kill session '634,63,@2';
System altered.

 

If this sessions are not killed by using kill session command, then use the Kill -9 OS command as follows.

Find the SPID of related session and execute the following command on Operating system.

 

$ kill -9 SPID

 

select 'kill -9 ' || P.SPID ,s.sid,S.USERNAME from v$session s ,v$process p where S.PADDR=P.ADDR and S.SID=163;

select 'kill -9 ' || P.SPID ,s.sid,S.USERNAME from v$session s ,v$process p where S.PADDR=P.ADDR and S.SID=634;



kill -9 15736

kill -9 16842

 

Execute this command on operating system, session will be killed forcily.

 

 

Now you can drop this user ( SCHEMA )

SQL> drop user MSD cascade;
User dropped.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/