[Oracle] DBMS_METADATA 패키지를 활용한 User 및 Tablespace DDL 추출하기

운영 업무를 하다 보면 기존 사용중인 DB계정 형태 그대로 사용하여 새로 User 또는 Tablespace, Table을 생성할 일이 빈번하다.

매번 Create 문장을 작성하기도 귀찮을 뿐 더러 계정 설정을 하나하나 확인하기도 쉽지는 않다.

 

그래서 오라클에서는 DBMS_METADATA라는패키지를 제공한다.

해당 패키지의 GET_DDL이라는 함수를 사용하면 해당 User의 Create 구문을 그대로 얻을 수 있다.

 

 

1. TABLE DDL 추출

SQL> set long 20000

SQL> set pagesize 500 

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

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

2. User DDL 추출

SQL> select dbms_metadata.get_ddl('USER','SCOTT') from dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP2"

3. TABLESPACE DDL 추출

SQL>  select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TEST" DATAFILE
  '/oracle/ora10/oradata/test2.dbf' SIZE 5242880,
  '/oracle/ora10/oradata/test1.dbf' SIZE 5242880,
  '/oracle/ora10/oradata/test3.dbf' SIZE 5242880
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

 

이 밖에도 Procedure, Function, Synonym등 다른 오브젝트들의 DDL 도 해당 기능을 사용하여 추출 할 수 있다.