Printable View
We have a Oracle 10g RAC database. I tried to drop a user ABC. Oracle gave 'ORA-01940: cannot drop a user that is currently connected' error. Although I have killed sessions that belong to user ABC. All current sessions belong to other users. Any ideas? Thank you.
that user is still connected - look at all instances
Thanks but I used gv$session.
well the user is still connected
User session might be rolling back, dead man walking but still connected and alive.
The gv$session shows no session (except the killed ones) for user ABC. I have altered system and enabled restricted session and tried to kill other user sessions. There are still sessions with username SYS, SYSMAN, and DBSNMP. I tried to kill these sessions but they come back to life right away. I tried to drop user ABC but got the same error 'ORA-01940: cannot drop a user that is currently connected'.
well there you go, they are still logged in - stop the app that is trying to connect and seriously dont kill sys processes - that would be stupid
Thanks but I only tried to delete the inactive sessions.
look, they are still connected - bounce the database, stop the apps do whatever, but you have to get rid of the connection
There are two possibilities when you see a session marked as KILLED.
1) Session is rolling back the transaction and you don't want to kill it.
You can check RBS activity for your session, if count goes down that means the poor guy is still rolling back.
2) Session just can't go away 'cause of the client connection is gone and the session is unable to send a message to the client.
If this is the case Oracle would clean the session but it might take time, sometimes entire days depending on size and activity of the database.
If you can bounce the instance as suggested by Davey23UK, this is the way to go.