Dropping All schema Objects using this Script

set spool On 

- it will create on.lst file on the home location 

- execute on.lst file using @on.lst at database this will drop all objects of the schema , 

some time in real Environment request comes like drop all objects and preserve Password of the schema , whcih means you just need to drop all Objects and keep the user available on the database, 


generally this is performed in Full Schema refresh activity, 


select 'drop '||object_type||' '||owner||'."'||object_name||'"'|| decode(object_type,'TABLE',' CASCADE CONSTRAINTS PURGE','CLUSTER PURGE',' INCLUDING 
TABLES CASCADE CONSTRAINTS PURGE')||';' from dba_objects
where owner in ('&owner') and object_type in ('TABLE','VIEW','SEQUENCE','PACKAGE','FUNCTION','PROCEDURE','SYNONYM','CLUSTER', 'JAVA SOURCE','TYPE','MATERIALIZED 
VIEW','TRIGGER')
order by 1 desc;



another script, 



select 'drop '||object_type ||' '|| owner||'.'||object_name||';' from dba_objects 

where owner in ('USERNAME');