Dropping connected users in Oracle database

oracle

Solution

Users are all capitals in `v$session` (and data dictionary views). If you match with capitals you should find your session to kill.

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
 /

Pass actual SID and SERIAL# values for user TEST then drop user...:

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'
/

Problem

I want to drop some users in Oracle DB using sqlplus but I am getting error: ``` SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected ``` I followed the link in SO to find out the sessions - Dropping a connected user from an Oracle 10g database schema But when I ran the command I am not getting any results: ``` SQL> select sid,serial# from v$session where username = 'test'; no rows selected ``` Please help me how to drop users in this case.

Original source

Related problems