I am in the process of putting my notes into web pages, please keep checking in later for more contents

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,
                   'TTYPE', 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
/
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 ncl_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,
                   'TTYPE', 11,
                   9) type_order,
  'drop '||object_type||' '||owner||'.'|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE','') sqlstmt
  from dba_objects
  where owner=upper('NCLFARE') 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
   ncl_drop_schema_objects('NCLFARE');
   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 ('CPLEX', 'NCLWEB')
and object_type not in ( 'INDEX', 'LOB');

spool drop_objects.sql
/
prompt purge dba_recyclebin;;
spool off

Home   Oracle