Database user session gets created after session kill

Question

After killing session by using ALTER SYSTEM KILL SESSION, I tried to drop the user, but it says the user is connected.

So when I again killed the session and tried to drop the user, I observed that each time I kill a session, immediately another session is getting created.

Example :

I run the query "Drop user cascade" : it says user connected cannot drop

I run the query:

SELECT 'ALTER SYSTEM KILL SESSION '''||s.sid||','|| s.serial#||''';'  
  FROM v$session s, v$process p 
 WHERE lower(s.username) = 'username'
  AND p.addr(+) = s.paddr and status='INACTIVE';

I get a query to kill sessions, now I kill all the sessions and run the drop user command again, it says user connected.

Checking for sessions again on the user shows a single inactive session which is getting created each time.

Answer 1

Dropping a user for an application that quickly reconnects may require a few steps:

1. Prevent the user from reconnecting with alter user username account lock
2. Kill the user's sessions with alter system kill session ...
3. Wait for any long-running transactions to rollback. Monitor the rollback progress by repeatedly running select used_urec, gv$transaction.* from gv$transaction
4. Drop the user with drop user username