To drop user objects

set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off

spool genera_dropallbmc_smgr.sql
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
from dual
union all
select 'spool dropall.log' DROP_OBJECTS
from dual
union all
Select
trim(case
when object_type = 'TABLE' then 'drop ' || object_type
|| ' ' || owner || '.' || object_name ||' cascade constraints'
when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
when object_type = 'INDEX' then 'prompt INDEXES'
when object_type = 'DATABASE LINK' then 'drop ' || object_type
|| ' ' || object_name
else 'drop ' || object_type || ' ' || owner || '.' || object_name
end || ';')
from dba_objects
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
union all
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
union all
select 'spool off'
from dual
;
spool off

exit