Search code examples
sqloracle-databaseoracle12c

Cannot drop disconnected oracle user

I am trying to drop a user from an Oracle DB (version 12c), but can not get it to work. The error I am getting is: 

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

Naturally I looked around, and found out how to forcibly disconnect and kill a session. So I used the query: 

select s.sid, s.serial#, status, s.username 
from v$session s 
where username = 'user_i_want_to_drop';

and then killed the only active session by using

alter system kill session '<sid>,<serial#>' IMMEDIATE;

naturally using the values from the query.

When I run the query again, it comes up empty, as expected. However, I still cannot drop the user and get the same error message.

I have noticed that when I query on gv$session, two sessions for that user show up. However, I cannot kill those using the alter system kill session statement I used above.

What am I missing here?

Solution

  • The other session(s) are connected to another instance of your cluster.

    Add inst_id to your gv$session query, like this:

    select sid,serial#,inst_id 
from gv$session 
where username = 'user_i_want_to_drop';

    Then, include the inst_id in the alter system kill session command, like this:

    alter system kill session '<sid>,<serial#>,@<inst_id>' immediate;

    E.g.,

    alter system kill session '15,1891,@1' immediate;