DBMS_METADATA

출처: http://blog.naver.com/sky777a?Redirect=Log&logNo=140119902755

/* 이 부분은 매번 스크립트 추출 시 공통으로 사용 */

SETtrimspool ON

SET heading off;

SET feedback off;

SET linesize 300

SET echo off;

SET pages 10000;

SET long 90000;

COL DDL FORMAT A10000

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,

'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM

,'STORAGE',false);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,

'CONSTRAINTS',false);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,

'REF_CONSTRAINTS',false);

/* 여기까지 공통으로 사용 */

▶ 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 - 문장 끝 공백 제거

▶ Table 추출

select dbms_metadata.get_ddl('TABLE', OBJECT_NAME, OWNER) DDL

FROM DBA_OBJECTS

WHERE OWNER IN ('SENDER')

AND OBJECT_TYPE = 'TABLE'

AND OBJECT_NAME NOT LIKE 'BIN$%';

/

▶ 인덱스 추출

select dbms_metadata.get_ddl('INDEX', INDEX_NAME, OWNER) DDL

FROM DBA_INDEXES

WHERE OWNER IN('SENDER')

/

▶ View 추출

select dbms_metadata.get_ddl('VIEW', VIEW_NAME, OWNER) DDL

FROM DBA_VIEWS

WHERE OWNER IN('SENDER')

/

▶ Trigger 추출

select dbms_metadata.get_ddl('TRIGGER', TRIGGER_NAME, OWNER) DDL

FROM DBA_TRIGGERS

WHERE OWNER IN('SENDER')

/

▶ Procedure 추출

select dbms_metadata.get_ddl('PROCEDURE', PROCEDURE_NAME, OWNER) DDL

FROM DBA_PROCEDURES

WHERE OWNER IN('SENDER')

/

[출처] DBMS_METADATA|작성자 매일우유