Drop Oracle user Objects |
---|
set head off set pagesize 0 set verify off set feedback off set linesize 180 trimspool on undefine my_username /* Avoid last define value of my_username */ define my_username=XXX select distinct object_type from dba_objects where owner=upper('&my_username') and object_type not in ( 'INDEX', 'LOB', 'PACKAGE BODY' ) order by 1; column type_order format 999 column sqlstmt format A120 select DECODE(OBJECT_TYPE, 'FUNCTION', 3, 'PROCEDURE', 3, 'PACKAGE', 3, 'VIEW', 7, 'MATERIALIZED VIEW', 8, 'TABLE', 10, 'TYPE', 11, 9) type_order, 'drop '||object_type||' '||owner||'.'|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE;',';') sqlstmt from dba_objects where owner=upper('&&my_username') and object_type not in ( 'INDEX', 'INDEX PARTITION', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'LOB', 'LOB PARTITION', 'PACKAGE BODY', 'TYPE BODY' ) and object_type not in ( 'DATABASE LINK', 'DIRECTORY' ) order by 1, 2; column type_order noprint spool drop_objects_for_schema_&&my_username..sql prompt set verify on echo on feedback on term on / prompt purge dba_recyclebin;; spool off column type_order print set pagesize 5000 set head on verify on feedback on |
rem verifying dropped: set feedback on pagesize 500 linesize 180 trimspool on column object_name format a32 select owner, object_name, object_type from dba_objects where owner=upper('&my_username'); |
Drop Oracle user Objects |
---|
-- select owner, object_name from dba_objects where owner='MYSCHEMA'; set serveroutput on declare procedure my_drop_schema_objects ( myusername in varchar2 ) as vMystatement varchar2(500) := ''; vMyTypeOrder integer := 0; CURSOR cMystatement IS select DECODE(OBJECT_TYPE, 'FUNCTION', 3, 'PROCEDURE', 3, 'PACKAGE', 3, 'VIEW', 7, 'MATERIALIZED VIEW', 8, 'TABLE', 10, 'TYPE', 11, 9) type_order, 'drop '||object_type||' '||owner||'.'|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE','') sqlstmt from dba_objects where owner=upper('&MY_USERNAME') and object_type not in ( 'INDEX', 'INDEX PARTITION', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'LOB', 'LOB PARTITION', 'PACKAGE BODY', 'TYPE BODY' ) and object_type not in ( 'DATABASE LINK', 'DIRECTORY' ) order by 1, 2; begin OPEN cMystatement; LOOP FETCH cMystatement INTO vMyTypeOrder, vMystatement; EXIT WHEN cMystatement%notfound; dbms_output.put_line ( vMystatement ); begin execute immediate vMystatement; exception when others then null; end; END LOOP; CLOSE cMystatement; end; BEGIN FOR i IN 1..10 LOOP -- i starts at 10, ends at 1 my_drop_schema_objects('MY_USERNAME'); DBMS_OUTPUT.PUT_LINE(i); -- statements here execute 10 times END LOOP; END; / |
Drop Oracle Schema Objects |
---|
-- gen_drop_schema_objects.sql set head off set pagesize 0 set verify off set feedback off purge dba_recyclebin; select 'drop '||object_type||' '||owner||'.'|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('MY_USERNAME1', 'MY_USERNAME2') and object_type not in ( 'INDEX', 'LOB'); spool drop_objects.sql / prompt purge dba_recyclebin;; spool off |
Drop Oracle Scheduler Jobs |
---|
-- gen_drop_scheduer_jobs.sql -- exec dbms_scheduler.drop_job(job_name => 'MY_JOB_OWNER.MY_JOB_NAME'); set feedback on pagesize 500 linesize 180 trimspool on select 'exec dbms_scheduler.drop_job(job_name => ''' || owner ||'.'||job_name || ''');' cmd from dba_scheduler_jobs where ( owner, job_name ) in ( select owner, object_name from dba_objects where owner='MY_USERNAME' and object_type='JOB' ); |