Good afternoon,
Folks!
Today I came to bring you a script that I used a lot and made my life a lot faster whenever I had to migrate a system from one instance to another or dump structure and data from one environment to another.
The script prompts for the name of the owner and a directory where it will generate the output files. The result of this script is SQL scripts containing DDL commands to exclude FK constraints, sequences, types, views, tables, procedures, functions, materialized views, public and private synonyms, and purge recyclebin tables. Also, in the generated scripts, it is already spooled to log the commands executed when executing them.
I use this script to delete all objects of an owner to have full control of what I am deleting and to log each object I am deleting. If it is a brave enough DBA, you can use the following command instead of this script:
| 1 | drop user <usuario> cascade; |
Let's go to the script drop_schema.sql:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | 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!
