ORACLE : How to DROP a User who is connected – forcefully

cannon The ideal way for all development shops to work is to have each developer have his own database, but as you all know life is not that easy on us!.

So you want to update the schema, which requires to drop all users and you see that your script is complaining that some of the users (in oracle users are the schemas) are still conneted and cannot be dropped. This   would normally mean some one is running an application/service/App server, which is holding some connections to your database.

You have been nice to all, mailed the group, pinged some of your fellow developers, but there is a rogue application holding on to a user/schema. What would you do.  wait for ever or take matter into your hands

Here is some Mojo and fire power for you to boot/kill those users force fully ( ofcourse you need to have SysDba access to the database, otherwise you are out of luck )

  • find the sessions for the users/schema , as a DBA use : select sid,serial# from v$session where username = '<user_schema>'
  • Then kill them with : alter system kill session '<sid>,<serial#>'

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

This will return one kill statement per session for that user – something like:

alter system kill session '375,64855';

alter system kill session '346,53146';

  • Now Drop the user : drop user <user_schema_name> cascade;

That’s it – The User is vaporized !



Advertisements

7 Comments

Filed under DB, ORACLE

7 responses to “ORACLE : How to DROP a User who is connected – forcefully

  1. karthik

    SQL> select sid,serial#,username from v$session where username= ‘IAD6_PROD’;

    no rows selected

    SQL> drop user IAD6_PROD cascade;
    drop user IAD6_PROD cascade
    *
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected

    SQL>

    what should i do here

  2. amos

    nothing; get some coffeee

  3. Wonderful post! We will be linking to this particularly
    great content on our website. Keep up the great writing.

  4. Thank you! This helped me out at work today.

  5. Eddy

    Same problem as karthik.
    @Amos: getting coffee didn’t help. It seems like there’s a phantom session active that’s blocking the user from being deleted.

  6. Helpful to drop a ghost user I didn’t locate on database. Thank you!

  7. Kushal Gangan

    Thanks, this was helpful 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s