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>
begin 
execute immediate 'alter user &USER2DROP. account lock';
for s in (select 'alter system kill session '''||sid||','||serial#||'''' stmt from v$session where SCHEMANAME = '&USER2DROP.') 
loop
  execute immediate s.stmt;
end loop;
dbms_lock.sleep(5);
execute immediate 'drop user &USER2DROP. cascade';
end;
/
Advertisements

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:

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

%d bloggers like this: