DBMS METADATA DDL

테이블,인덱스,프로시저의 생성 스크립트 추출

DBMS_METADATA 패키지의  GET_DDL 함수 사용

Tablespace 추출

SPOOL cre_tablespace.sql
 
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL
  FROM DBA_TABLESPACES
 WHERE TABLESPACE_NAME NOT IN ('SYS', 'SYSAUX','SYSTEM','TEMP', 'USERS', 'TOOLS');
 
SPOOL OFF
 
-- 처음 공백 제거
HOST perl -e's/^(공백) //g' cre_tablespace.sql
  
-- 문장 끝 공백 제거
HOST perl -e's/(공백)$//g' cre_tablespace.sql

테이블 생성 스크립트

SPOOL cre_table.sql
 
SELECT DBMS_METADATA.GET_DDL('TABLE', OBJECT_NAME, OWNER) DDL
  FROM DBA_OBJECTS
 WHERE OWNER IN ('WISEU')
   AND OBJECT_TYPE = 'TABLE'
   AND OBJECT_NAME NOT LIKE 'BIN$%';
    
SPOOL OFF
 
HOST perl -e's/^ //g' cre_table.sql
HOST perl -e's/ $//g' cre_table.sql

인덱스 생성 스크립트

SELECT DBMS_METADATA.GET_DDL('INDEX','IX_BOARD','OWNER') FROM DUAL;

테이블/PK인덱스 생성 스크립트

SELECT A.OWNER
     , A.TABLE_NAME
     , B.CONSTRAINT_NAME
--     , C.COLUMN_NAME 
--     , COUNT(*) OVER (PARTITION BY A.OWNER,B.CONSTRAINT_NAME) AS DUP
     , 'SELECT DBMS_METADATA.GET_DDL('''||'TABLE'||''','''||A.TABLE_NAME||''','''||A.OWNER||''') FROM DUAL;' AS TBL_DDL
     , 'SELECT DBMS_METADATA.GET_DDL('''||'INDEX'||''','''||B.CONSTRAINT_NAME||''','''||A.OWNER||''') FROM DUAL;' AS IDX_DDL
     
  FROM ALL_TABLES       A
     , ALL_CONSTRAINTS  B
     , ALL_CONS_COLUMNS C
     , MIG_TABLES       D  
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.OWNER      = B.OWNER 
   AND D.TABLE_NAME = B.TABLE_NAME
   AND D.OWNER      = B.OWNER 
   AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
   AND B.CONSTRAINT_TYPE = 'P'
   
   AND D.PARTITION_YN = 'Y'   
 GROUP BY A.OWNER
     , A.TABLE_NAME
     , B.CONSTRAINT_NAME

프로시져 생성 스크립트

SELECT DBMS_METADATA.GET_DDL('PROCEDURE','P_BOARD') FROM DUAL;

시퀀스 생성 스크립트

SELECT DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_BOARD') FROM DUAL;

SQLPLUS 로 생성스크립트 파일로 저장하기

- 테이블, 인덱스, 시퀀스, 프로시저 생성 스크립트를 파일로 저장.

set pagesize 0
set long 90000
set feedback off
set echo off 
spool board.sql 

SELECT DBMS_METADATA.GET_DDL('TABLE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='TABLE';
SELECT DBMS_METADATA.GET_DDL('INDEX', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='INDEX';
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='SEQUENCE';
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME) FROM USER_OBJECTS U WHERE OBJECT_TYPE='PROCEDURE';

spool off

트리거 추출

SPOOL cre_trigger.sql
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM   all_triggers
WHERE  owner        = UPPER('&1')
AND    trigger_name = DECODE(UPPER('&2'), 'ALL', trigger_name, UPPER('&2'));
SPOOL OFF
 
HOST perl -e's/^ //g' cre_trigger.sql
HOST perl -e's/ $//g' cre_trigger.sql