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.