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');

-- 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;
/

-- 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

-- 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'
);