Drop all objects of a schema-
Pre-requisite- (Very Important)
We have to login in particular schema of which we want to drop all objects. Otherwise it will drop all the objects of SYSTEM and other important table and views.
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;
/