[펌]dbms_metadata 패키지 (테이블 스크립트 가져오기)

9i 의 새로운 기능으로 DBMS_METADATA package 를 이용한다.
먼저 view 를 생성하기 위해 $ORACLE_HOME/rdbms/admin/catmeta.sql script 를 sys user 로 실행한다.


DBMS_METADATA pacakge 는 schema object 의 완전한 definition 을 얻기 위한 강력한 tool 로서 한번에object 의 모든 attributes 를 얻을 수 있다.

FUNCTION GET_DDL RETURNS CLOB

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_TYPE                    VARCHAR2                IN
 NAME                           VARCHAR2                IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 VERSION                        VARCHAR2                IN     DEFAULT
 MODEL                          VARCHAR2                IN     DEFAULT
 TRANSFORM                      VARCHAR2                IN     DEFAULT

예제]

SQL> set long 1000000
SQL> set pages 100
SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(4,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



SQL> select dbms_metadata.GET_DEPENDENT_DDL('INDEX','DEPT','SCOTT') from dual ;


DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','DEPT','SCOTT')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


그리고 위의 script 에서 storage 절을 없이 원한다면
다음의 procedure 를 먼저 실행한다.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
'STORAGE',false);



SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER) "A",
       DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME,OWNER) "B",
       DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT',TABLE_NAME,OWNER) "C"
  FROM DBA_TABLES
 WHERE OWNER = 'SCOTT'
   AND TABLE_NAME = 'EMP';


A
--------------------------------------------------------------------------------
B
--------------------------------------------------------------------------------
C
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(14,0),
        "ENAME" VARCHAR2(30),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(10,2),
        "COMM" NUMBER(10,2),
        "DEPTNO" NUMBER(4,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 23986176 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 68681728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

  CREATE INDEX "SCOTT"."IDX_ENAME" ON "SCOTT"."EMP" ("ENAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 30932992 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 23986176 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

  COMMENT ON COLUMN "SCOTT"."EMP"."EMPNO" IS '사원번호'


출처 : http://blog.naver.com/nv2921/150046036187