Oracle 전체 DDL 문장 뽑아내기

set lin 200
set heading off
set verify off
set term off
set long 90000

PROMPT ********************** user **************************
PROMPT *                                                      *
PROMPT *******************************************************
PROMPT
spool user.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool user.log
select 'select dbms_metadata.get_ddl','(''USER'',' ,''''||t.username||''') from dual;'
from dba_users t
where username not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT ********************** table **************************
PROMPT *                                                      *
PROMPT *******************************************************
PROMPT
spool table.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool table.log
select 'select dbms_metadata.get_ddl','(''TABLE'',' ,''''||t.table_name||''','''||owner||''') from dual;'
from dba_tables t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT *********************** tablespace ********************
PROMPT *                                                      *
PROMPT *******************************************************
PROMPT
spool tablespace.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool tablespace.log
select 'select dbms_metadata.get_ddl','(''TABLESPACE'',' ,''''||t.tablespace_name||''') from dual;'
from dba_tablespaces t
where tablespace_name not in ('SYS','SYSTEM','SYSAUX','TEMP','USERS','TOOLS')
/
PROMPT spool off
spool off

PROMPT *********************** index **************************
PROMPT *                                                       *
PROMPT ********************************************************
PROMPT
spool index.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool index.log
select 'select dbms_metadata.get_ddl','(''INDEX'',' ,''''||t.index_name||''','''||owner||''') from dual;'
from dba_indexes t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT ********************** view ****************************
PROMPT *                                                       *
PROMPT ********************************************************
PROMPT
spool view.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool view.log
select 'select dbms_metadata.get_ddl','(''VIEW'',' ,''''||t.view_name||''','''||owner||''') from dual;'
from dba_views t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT *********************** Trigger *************************
PROMPT *                                                        *
PROMPT *********************************************************
spool trigger.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool trigger.log
select 'select dbms_metadata.get_ddl','(''TRIGGER'',' ,''''||t.trigger_name||''','''||owner||''') from dual;'
from dba_triggers t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT ************************Procedure ***********************
PROMPT *                                                        *
PROMPT *********************************************************
PROMPT
spool procedure.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool procedure
select 'select dbms_metadata.get_ddl','(''PROCEDURE'',' ,''''||t.procedure_name||''','''||owner||''') from dual;'
from dba_procedures t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off

PROMPT ************************sequence ***********************
PROMPT *                                                        *
PROMPT *********************************************************
spool sequence.sql
PROMPT set lin 200
PROMPT set heading off
PROMPT set verify off
PROMPT set term off
PROMPT set long 90000
PROMPT spool sequence
select 'select dbms_metadata.get_ddl','(''SEQUENCE'',' ,''''||t.SEQUENCE_NAME||''','''||SEQUENCE_OWNER||''') from dual;'
from dba_sequences t
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MGMT_VIEW','WMSYS','DIP','TSMSYS','ORACLE_OCM')
/
PROMPT spool off
spool off