Dropping User

To drop a user you must have the DROP USER system privilege

To check if user has privilege to drop user.

select * from session_privs where privilege=’DROP USER’;

 

Dropping User without having objects:

SQL> drop user test;

 

SQL> drop user a cascade;

Dropping a Connected User

Find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'TEST';



SID SERIAL#
---------- ----------
268 1268
315 1223

Before killing the user , lock its account so that it cannot attempt
further connections.

SQL> Alter user test account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;

SQL> alter system kill session ‘315,1223’;

Finally drop the user.

SQL> drop user test cascade;

Advertisements

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: