How to avoid ORA-01940: cannot drop a user that is currently connected?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to avoid ORA-01940: cannot drop a user that is currently connected?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    122

    How to avoid ORA-01940: cannot drop a user that is currently connected?

    Hi,

    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?

    thanks

  2. #2
    Join Date
    Sep 2007
    Posts
    36
    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.
    J

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    REVOKE "CONNECT" FROM offending_user; should do it.

    A more dramatic alternative might be...
    stop listener
    kill all offending user sessions
    drop the user
    start listener

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width