Drop All Objects In an Oracle Schema

In earlier post, we talk about how to drop all tables in an Oracle Schema. Here we are going to present a script that drop all the database objects in a schema including tables, views, functions, procedures, mining objects, etc. Please see the following PL/SQL script.

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

begin

for i in 1..3 loop
for r in (select object_name, object_type from user_objects 
          order by object_type, object_name)
  loop
  begin
    if (r.object_type = 'MINING MODEL') then 
     execute immediate ' begin dbms_data_mining.drop_model('||''''||
       r.object_name||''''||'); end;';
    elsif (r.object_type = 'TABLE') then
      execute immediate 'drop table "'||r.object_name
                    ||'" cascade constraints purge';
    else
       execute immediate 'drop '||r.object_type||' "'||r.object_name||'"';
    end if;
    exception when others then null;
  end;
  end loop;
end loop;
end;
/

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

In the above scripts, there are a number of things that we want to mentions.
1. Mining objects are predictive models. A mining object may contain tables with names starting with DM$. 2. The top level loop of for i in 1..3 is to remove all the objects three times. The is because objects may have dependency relationships. Objects may not be dropped if other objects are dependent on them. The simple solution to this is to try to drop all objects several times.
I saved it as file this_will_drop_all_user_objects.sql. I logged in my schema using sqlplus and run the scripts.

$sqlplus myschema/mypassword

SQL> @this_will_drop_all_user_objects 

PL/SQL procedure successfully completed.

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