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

SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected









Reason - User connected to the database.To Drop user ask user to disconnect or Do

 it by yourself.



To Disconnect the session .Check for the SID and SPID using this command-



SELECT s.sid, s.serial#, s.status, p.spid 
  FROM v$session s, v$process p 
 WHERE s.username = 'test' 
  AND p.addr(+) = s.paddr
 /



It will Give output like this---



       SID    SERIAL# STATUS   SPID
---------- ---------- -------- ------------------------
       162          9 ACTIVE   29613



Now Use this command to kill session.



alter system kill session 'SID,SPID';



alter system kill session '162,9';



It may take time to kill session. 



Thanks For reading this blog. -Saurabh