How to Drop SCHEMA Objects in Oracle

How to Drop SCHEMA Objects in Oracle

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_obj.sql

select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by  object_type,object_name
/

spool off;
set head on

@ target_schemaname_drop_obj.sql
exit

DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300

spool target_schemaname_drop_other_obj.sql

select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/

spool off;
set head on

@target_schemaname_drop_other_obj.sql
exit

ls -lrth target_schemaname_drop*.sql

vi target_schemaname_drop_obj.sql  /  vi target_schemaname_drop_other_obj.sql

:wq

sqlplus "/as sysdba"

@target_schemaname_drop_obj.sql

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

exit