Site icon IT Tutorial

Drop User Cascade in Oracle

Hi,

I will explain how to Drop User Cascade in Oracle in this post.

 

You can remove a database user and user’s objects with executing DROP USER command.

If you drop a user, Oracle removes and purges all of that user’s schema objects from the recycle bin.

DROP USER Syntax
DROP USER Syntax is as follows.
DROP USER username [CASCADE];

You can get the following error during the Drop User.

 

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

 

 

To drop any user, you need to lock related user firstly.

SQL> alter USER MSD account lock;

User altered.

 

Then kill the session of related user as follows.

SQL> select s.sid, s.serial#, s.status, p.spid from gv$session s, gv$process p where s.username = 'MSD' and p.addr (+) = s.paddr;
SID SERIAL# STATUS SPID
——— ———- ——– ————
163 34 INACTIVE 342463
634 63 INACTIVE 153783

 

 

 

Kill these sessions as follows.

SQL> alter system kill session '163,263';
System altered.

SQL> alter system kill session '634,63';
System altered.

 

Now you can drop this user ( SCHEMA )

SQL> drop user MSD cascade;
User dropped.

 

 

Do you want to learn Oracle Priviliges, then read the following articles.

How to Grant and Revoke Privileges | Create and Drop any User in Oracle Database

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 1,839 views last month,  1 views today

Exit mobile version