다른 명령
테이블,인덱스,프로시저의 생성 스크립트 추출
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