CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IS
cursor obj_crs is
select owner, object_name, object_type
from all_objects
where owner = upper(P_Owners);
obj_REC obj_CRS%ROWTYPE;
l_grants_objects varchar2(500);
BEGIN
dbms_output.put_line('Begining to drop all objects');
Case upper(P_Owners)
when 'SYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'SYSTEM' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'WMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'TSMSYS' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'OUTLN' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
when 'DBSNMP' then dbms_output.put_line('Can drop objects for:'||upper(P_Owners));
ELSE
Begin
FOR obj_REC IN obj_CRS LOOP
begin
dbms_output.put_line('OUT-1 -> ' || obj_REC.object_type);
CASE obj_REC.object_type
WHEN 'TABLE' THEN
l_grants_objects := 'drop table '||obj_REC.owner||'.'||obj_REC.object_name||' cascade constraints PURGE';
EXECUTE IMMEDIATE l_grants_objects;
-- WHEN 'MATERIALIZED VIEW' THEN
-- dbms_output.put_line('OUT-2 -> INSIDE MATERIALIZED VIEW');
-- l_grants_objects := 'drop MATERIALIZED VIEW ' || obj_REC.owner||'.'||obj_REC.object_name ;
-- dbms_output.put_line(' OUT-2 -> ' || l_grants_objects);
-- EXECUTE IMMEDIATE l_grants_objects;
WHEN 'INDEX' THEN l_grants_objects := '';
WHEN 'TYPE' THEN
l_grants_objects := 'drop type '||obj_REC.owner||'.'||obj_REC.object_name||' force';
EXECUTE IMMEDIATE l_grants_objects;
ELSE
dbms_output.put_line('OUT-2222** -> INSIDE MATERIALIZED VIEW');
l_grants_objects := 'drop '||obj_REC.object_type||' '||obj_REC.owner||'.'||obj_REC.object_name;
EXECUTE IMMEDIATE l_grants_objects;
END CASE;
dbms_output.put_line(l_grants_objects);
Exception
when others then
l_grants_objects := '';
end;
end loop;
end;
end case;
dbms_output.put_line('All objects are dropped');
End;
/
Ex:- exec DROP_OBJECTS('user_name');
