-
drop a user in a 10g RAC database
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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'.
Last edited by jiong; 03-20-2007 at 11:49 AM.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|