Drop User in Oracle

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 connected

The 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 privileges

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

Drop All Tables In an Oracle Schema

Sometimes, we want to drop all tables in our schema. To do this, I create the following PL/SQL script file.

               Warning!!!!! The script below will remove all tables 
                       under the current schema!!!

begin
for r in (select table_name from user_tables order by table_name)
  loop
  begin
   execute immediate 'drop table '||r.table_name
                     ||' cascade constraints purge';
   exception when others then null;
  end;
  end loop;
end;
/

               Warning!!!!! The script above will remove all tables 
                       under the current schema!!!

I saved it as file this_will_drop_all_user_tables.sql. I logged in my schema using sqlplus and run the scripts.

$sqlplus myschema/mypassword

SQL> @this_will_drop_all_user_tables 

PL/SQL procedure successfully completed.

All the tables are removed under the current schema. Again, please be careful before run the script! It will remove all tables under the current schema.