Delete all objects from a schema

Question

I have a database on oracle where I need delete all objects of a particular Schema, I cannot exclude the Schema, because the validation of the system is based on an algorithm that takes into account the ID of the Schema.

Answer 1

Below is a script to delete all objects of an Owner, have control of what is deleting and generate a log of each object to be deleted.

set verify off
set heading off
set feedback off

accept   vo prompt 'Informe o Owner: '
accept   dir prompt 'Diretorio de spool: '
column global_name new_value instancia noprint
select replace(global_name, '.WORLD', '') global_name from global_name;
spool "&dir\&Vo._&instancia._drop_obj_schema.sql"
prompt spool "&dir\&Vo._&instancia._drop_obj_schema.log"

prompt
prompt set echo on
prompt

select   'ALTER TABLE '||OWNER||'."'||TABLE_NAME||'" DROP CONSTRAINT "'||CONSTRAINT_NAME||'";'
from     dba_constraints
where    owner = upper('&Vo')
and      constraint_type = 'R'
/

select   DISTINCT 'DROP SEQUENCE '||SEQUENCE_OWNER||'."'||SEQUENCE_NAME||'";'
from     dba_sequences
where    sequence_owner = upper('&Vo')
/

select   DISTINCT 'DROP '||TYPE||' '||OWNER||'."'||NAME||'";'
from     dba_source
where    owner = upper('&Vo')
/

select   'DROP VIEW '||OWNER||'."'|| VIEW_NAME||'";'
from     dba_views
where    owner = upper('&Vo')
/

select   'DROP TABLE '||OWNER||'."'||TABLE_NAME||'" PURGE;'
from     dba_tables
where    owner = upper('&Vo')
/

select   'DROP SYNONYM '||OWNER||'."'||SYNONYM_NAME||'";'
from     dba_synonyms
where    owner = upper('&Vo')
/

select   'DROP TYPE '||OWNER||'."'||TYPE_NAME||'";'
from     dba_types
where    owner = upper('&Vo')
/

select   'DROP MATERIALIZED VIEW '||OWNER||'."'||MVIEW_NAME||'";'
from     dba_mviews
where    owner = upper('&Vo')
/

SELECT 'PURGE TABLE ' || OWNER || '."' || ORIGINAL_NAME  || '";'
FROM dba_recyclebin 
WHERE owner = upper('&Vo')
and type = 'TABLE'
/

prompt
prompt set echo off
prompt

prompt
prompt spool off
spool off

spool "&dir\&Vo._&instancia._drop_syn.sql"
prompt spool "&dir\&Vo._&instancia._drop_syn.log"

prompt
prompt set echo on
prompt

select 'drop public synonym ' || synonym_name || ';'
from dba_synonyms
where table_owner = upper('&Vo')
and owner = 'PUBLIC'
and db_link is null
order by synonym_name
/
prompt
prompt
prompt

select 'drop synonym ' || owner || '.' || synonym_name || ';'
from dba_synonyms
where table_owner = upper('&Vo')
and owner != 'PUBLIC'
and db_link is null
order by owner, synonym_name
/

prompt
prompt
prompt set echo off
prompt

prompt
prompt spool off
spool off

undef vo
undef dir
undef instancia

set verify on
set heading on
set feedback on

Source: http://www.dirceuresende.com/blog/apagando-todos-os-objetos-de-um-owner-no-oracle-database/