Deleting Objects from a Schema in Oracle

Here is a script that would come handy if you need to delete all the objects in a schema. Please ensure you are NOT logged in as System / Sys. Log in as the user for whose schema all the objects need to be dropped. Hope this helps.

--

prompt >>>

prompt >>> dropping it all..................

prompt >>>

--

begin

declare

cursor c1 is

select table_name, constraint_name from user_constraints where constraint_type = 'R';

cursor c2 is

select table_name, constraint_name from user_constraints where constraint_name not like 'SYS_IL%';

cursor c3 is

select index_name from user_indexes where index_name not like 'SYS_IL%';

cursor c4 is

select table_name, constraint_name from user_constraints where constraint_type = 'P';

cursor c5 is

select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';

cursor c6 is

select table_name from user_tables;

cursor c7 is

select object_name from user_objects where object_type = 'PROCEDURE';

cursor c8 is

select sequence_name from user_sequences;

v_sql varchar2(512);

begin

for c8_rec in c8 LOOP

v_sql := 'drop sequence '||c8_rec.sequence_name;

execute immediate v_sql;

END LOOP;

for c1_rec in c1 LOOP

v_sql := 'alter table '||c1_rec.table_name||' drop constraint '||c1_rec.constraint_name;

execute immediate v_sql;

END LOOP;

for c2_rec in c2 LOOP

v_sql := 'alter table '||c2_rec.table_name||' drop constraint '||c2_rec.constraint_name||' drop index';

execute immediate v_sql;

END LOOP;

for c3_rec in c3 LOOP

v_sql := 'drop index '||c3_rec.index_name;

execute immediate v_sql;

END LOOP;

for c4_rec in c4 LOOP

v_sql := 'alter table '||c4_rec.table_name||' drop constraint '||c4_rec.constraint_name||' drop index';

execute immediate v_sql;

END LOOP;

for c5_rec in c5 LOOP

v_sql := 'drop index '||c5_rec.index_name;

execute immediate v_sql;

END LOOP;

for c6_rec in c6 LOOP

v_sql := 'drop table '||c6_rec.table_name||' cascade constraints';

execute immediate v_sql;

END LOOP;

for c7_rec in c7 LOOP

v_sql := 'drop procedure '||c7_rec.object_name;

execute immediate v_sql;

END LOOP;

end;

end;

/