Good afternoon,
Guys!

Today I came to bring you a script that I've used a lot and it made my life a lot faster whenever I had to migrate a system from one instance to another or dump the structure and data from one environment to another.

The script requests the name of the owner and a directory where it will generate the output files. The result of this script are SQL scripts containing DDL commands to delete FK constraints, sequences, types, views, tables, procedures, functions, materialized views, public and private synonyms and purge the recyclebin tables. Furthermore, in the generated scripts, spooling is already done to generate a log of the commands executed when executing them.

I use this script to delete all objects from an owner to have full control of what I am deleting and generate a log of each object I am deleting. If you are a brave enough DBA, you can use the following command instead of this script:

drop user <usuario> cascade;

Let's go to the 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

Thank you and see you next time!