Monday, April 14, 2014

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.

4 comments:

Unknown said...

good one !! really helpful

Visit Oracle Training

Unknown said...

I want to shine in a oracle field, so I am looking the information related to database. Your blog really have some more useful information about testing. Keep your blog with oracle updates…
Regards,
Oracle DBA Training in Chennai|Oracle Training Institute in Chennai

Nandhini said...

The strategy you have posted on the oracle hepled me to get into the next level of this technology and had lot of informations in it.
oracle training in chennai | oracle training institutes in chennai

Unknown said...

Much obliged to you for requiring significant investment to give us a portion of the valuable and restrictive data with us.
Software Testing Training in chennai | Testing Training in chennai | Software Training institutes in chennai