Buenas tardes,
¡Tipo!

Hoy vengo a traerles un script que he usado mucho y me hacía la vida mucho más rápida siempre que tenía que migrar un sistema de una instancia a otra o volcar la estructura y los datos de un entorno a otro.

El script solicita el nombre del propietario y un directorio donde generará los archivos de salida. El resultado de este script son scripts SQL que contienen comandos DDL para eliminar restricciones FK, secuencias, tipos, vistas, tablas, procedimientos, funciones, vistas materializadas, sinónimos públicos y privados y purgar las tablas de la papelera de reciclaje. Además, en los scripts generados ya se realiza el spooling para generar un log de los comandos ejecutados al ejecutarlos.

Utilizo este script para eliminar todos los objetos de un propietario para tener control total de lo que estoy eliminando y generar un registro de cada objeto que estoy eliminando. Si eres un DBA lo suficientemente valiente, puedes usar el siguiente comando en lugar de este script:

drop user <usuario> cascade;

vamos al guión 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

¡Gracias y hasta la próxima!