Post Drop All Objects In an Oracle Schema uses PL/SQL to iteratively drop database objects. We can simply drop a user and its database objects in Oracle using "drop user.. cascade". This provides an easy way to clean up the database. We have to drop a user by connecting to another user. If we try to drop a user that we are currently connecting to, we will get error ORA-01940. The Sqlplus session is connected to user1.
SQL> drop user user1 cascade; drop user user1 cascade * ERROR at line 1: ORA-01940: cannot drop a user that is currently connectedThe user that we are connecting to needs to have "drop user" privilege. In the following example, the Sqlplus session is connected to user2.
SQL> drop user user1 cascade; rop user user1 cascade RROR at line 1: RA-01031: insufficient privilegesWe connected to sys and grant "drop user" privilege to user2.
SQL> grant drop user to user2; Grant succeeded.Now we connect to user2 and are able to drop user1
SQL> drop user user1 cascade; User dropped.