Created
February 11, 2017 17:11
-
-
Save ibre5041/198cf54fe0ecfb10e445043cf29f6ddc to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set serveroutput on size unlimited | |
declare | |
v_ItemCount integer; | |
begin | |
SELECT count(*) | |
INTO v_ItemCount | |
FROM ALL_OBJECTS AO | |
WHERE AO.OWNER = '&USER' | |
AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB') | |
AND AO.OBJECT_NAME NOT LIKE 'BIN$%'; | |
while (v_ItemCount > 0) loop | |
for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || '"'||AO.OWNER||'"'|| '.' || '"'||AO.OBJECT_NAME||'"' || | |
DECODE(AO.OBJECT_TYPE, | |
'TABLE', | |
' CASCADE CONSTRAINTS', | |
'') as DROPCMD, | |
AO.OWNER, | |
AO.OBJECT_TYPE, | |
AO.OBJECT_NAME | |
FROM ALL_OBJECTS AO | |
WHERE AO.OWNER = '&USER' | |
AND AO.OBJECT_TYPE NOT IN ('INDEX', 'LOB') | |
AND AO.OBJECT_NAME NOT LIKE 'BIN$%') | |
loop | |
begin | |
if v_Cmd.OBJECT_TYPE = 'SCHEDULE' then | |
DBMS_SCHEDULER.DROP_SCHEDULE('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true); | |
ELSIF v_Cmd.OBJECT_TYPE = 'JOB' then | |
DBMS_SCHEDULER.DROP_JOB('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true); | |
ELSIF v_Cmd.OBJECT_TYPE = 'PROGRAM' then | |
DBMS_SCHEDULER.DROP_PROGRAM('"'||v_Cmd.OWNER||'"'||'.'||'"'||v_Cmd.OBJECT_NAME||'"', true); | |
else | |
execute immediate v_Cmd.dropcmd; | |
end if; | |
dbms_output.put_line(v_Cmd.dropcmd); | |
exception | |
when others then | |
null; -- ignore errors | |
end; | |
end loop; | |
SELECT count(*) | |
INTO v_ItemCount | |
FROM ALL_OBJECTS AO | |
WHERE AO.OWNER = '&USER' | |
AND AO.OBJECT_TYPE NOT IN ('INDEX','LOB') | |
AND AO.OBJECT_NAME NOT LIKE 'BIN$%'; | |
end loop; | |
execute immediate 'purge dba_recyclebin'; | |
end; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment