Boa tarde,
Pessoal!

Hoje vim trazer um script pra vocês que eu já usei muito e agilizava muito a minha vida sempre que eu tinha que migrar um sistema de uma instância para outra ou fazer um dump de estrutura e dados de um ambiente para o outro.

O script solicita o nome do owner e um diretório onde irá gerar os arquivos de saída. O resultado desse script são scripts SQL contendo os comandos DDL para excluir constraints de FK, sequences, types, views, tabelas, procedures, functions, views materializadas, sinônimos públicos e privados e expurgar as tabelas da recyclebin. Além disso, nos scripts gerados, já é feito o spool para gerar log dos comandos executados ao executá-los.

Eu utilizo esse script para apagar todos os objetos de um owner para ter o total controle do que estou excluindo e gerar um log de cada objeto que estou excluindo. Se for for um DBA corajoso o bastante, pode usar o seguinte comando ao invés desse script:

drop user <usuario> cascade;

Vamos ao script drop_schema.sql:

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

Obrigado e até a próxima!