How to avoid ORA-01940: cannot drop a user that is currently connected?
I tried to drop a user, it has been used by application, I have used alter system kill session command to kill the connection, but these applications keep reconnecting to the user so I can't drop the user, is there a way to prevent future connections to the user?
I haven't tried this on a connected user, so no assurances here..
alter user xxxxxx revoke connect
there may be some other roles that need revoking, but whether you can do this to a connected user, you'll have to find out.
Let me know if it works.
REVOKE "CONNECT" FROM offending_user; should do it.
A more dramatic alternative might be...
kill all offending user sessions
drop the user
Last edited by PAVB; 09-24-2007 at 01:34 PM.
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.
Click Here to Expand Forum to Full Width