DROP_OBJECTS.SQL

1.Copy the below procedure to a file and execute the file from sys or system user.


CREATE OR REPLACE PROCEDURE DROP_OBJECTS(P_Owners varchar2) IScursor obj_crs isselect owner, object_name, object_typefrom all_objectswhere 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;/

2.Login to db as super user(sys,system) and execute the procedure replacing value with the username.

eg:-

sqlplus system/password@dbname

exec drop_objects('USERNAME');

exec drop_objects('IDS');