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 '사원번호'