How-to drop an active oracle user

If we want to drop a user who is actively connecting to the DB, we can use this small PL/SQL procedure:

define USER2DROP = <User Name>
execute immediate 'alter user &USER2DROP. account lock';
for s in (select 'alter system kill session '''||sid||','||serial#||'''' stmt from v$session where SCHEMANAME = '&USER2DROP.') 
  execute immediate s.stmt;
end loop;
execute immediate 'drop user &USER2DROP. cascade';

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: