drop a user in a 10g RAC database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: drop a user in a 10g RAC database

  1. #1
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    that user is still connected - look at all instances

  3. #3
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    Thanks but I used gv$session.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well the user is still connected

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  6. #6
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    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 12:49 PM.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  8. #8
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    Thanks but I only tried to delete the inactive sessions.

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    look, they are still connected - bounce the database, stop the apps do whatever, but you have to get rid of the connection

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  


Click Here to Expand Forum to Full Width