Use the following script to drop all objects in a specific schema in an Oracle Database without droping the schema itself. It saves you from recreating the schema, its associated user, its rights, etc.
create script file
Create a file called
empty_user.sql with the following content:
purge recyclebin; declare -- FK first, then unique, then PK cursor cursor_constraints is select table_name, constraint_name from user_constraints where constraint_type in ('P', 'R', 'U') order by decode(constraint_type, 'R', 0, 'U', 1, 'P', 2, 3); cursor cursor_mviews is select mview_name from user_mviews; cursor cursor_views is select view_name from user_views; cursor cursor_mviews_logs is select master from user_mview_logs; cursor cursor_tables is select table_name from user_tables; cursor cursor_synonyms is select synonym_name from user_synonyms; cursor cursor_sequences is select sequence_name from user_sequences; begin for current_val in cursor_constraints loop execute immediate 'alter table ' || current_val.table_name || ' drop constraint ' || current_val.constraint_name; end loop; for current_val in cursor_mviews loop execute immediate 'drop materialized view ' || current_val.mview_name; end loop; for current_val in cursor_views loop execute immediate 'drop view ' || current_val.view_name; end loop; for current_val in cursor_mviews_logs loop execute immediate 'drop materialized view log on ' || current_val.master; end loop; for current_val in cursor_tables loop execute immediate 'drop table ' || current_val.table_name || ' purge'; end loop; for current_val in cursor_synonyms loop execute immediate 'drop synonym ' || current_val.synonym_name; end loop; for current_val in cursor_sequences loop execute immediate 'drop sequence ' || current_val.sequence_name; end loop; end; / quit
line with “/” is required to execute the preceding PL/SQL procedure
line with “quit” is usefull to automatically exit sqlplus when procedure has been executed
Execute with sqlplus
sqlplus user/user_pwd @empty_user.sql
You can expect output such as the following:
my_machine:$ sqlplus user/user_pwd @empty_user.sql SQL*Plus: Release 188.8.131.52.0 Production on Tue Oct 14 12:27:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Recyclebin purged. PL/SQL procedure successfully completed. Disconnected from Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
The origin of the above script is one largely used at Ekino to which multiple developers (including myself) contributed.