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 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.