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.