출처 : http://jinh2004.blog.me/80123657217
"dbms_metadata" package는 Oracle10g부터 제공된 package로,
해당 objects를 생성할 때 사용된 DDL(Data Difinition Language)을 보여줍니다.
예전부터 알고 있어서 평소에도 자주 사용했었는데,
예를 들어 "emp"라는 table에 걸려있는 index들의 DDL을 얻고 싶은 경우,
다음과 같이 query를 발행했습니다.
|
SQL> select index_name from user_indexes where table_name = 'EMP';
INDEX_NAME ---------------------------------------- PK_EMP UK_EMP
SQL> select dbms_metadata.get_ddl('INDEX', 'PK_EMP', 'SCOTT') from dual;
dbms_metadata.get_ddl('INDEX','PK_EMP','SCOTT') -------------------------------------------------------------------------------- CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") ...생략... ) TABLESPACE "USERS"
SQL> select dbms_metadata.get_ddl('INDEX', 'UK_EMP', 'SCOTT') from dual;
dbms_metadata.get_ddl('INDEX','UK_EMP','SCOTT') -------------------------------------------------------------------------------- CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("ENAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "USERS"
SQL> |
그런데 위의 방식은 몇 번이나 query를 발행해야 해서 번거롭습니다.
최근에 알게 된 내용인데, 다음과 같이 하면 1번의 query로 동일한 결과를 얻을 수 있습니다.
|
SQL> select dbms_metadata.get_ddl('INDEX', index_name, 'SCOTT') 2 from user_indexes where table_name = 'EMP'; |
혹은 "user"라는 reserved keyword를 이용해서,
|
SQL> select dbms_metadata.get_ddl('INDEX', index_name, user) 2 from user_indexes where table_name = 'EMP'; |
와 같이 실행하여 동일한 결과를 얻을 수 있습니다.
만약 "scott" schema내의 모든 objects에 대한 DDL정보를 얻고자 하는 경우에는,
다음과 같이 실행할 수 있습니다.
|
SQL> select dbms_metadata.get_ddl(object_type, object_name, user) 2 from user_objects; |
이제 이 DDL을 생성하는 query의 내용을 변경하는 방법에 대해 다루겠습니다.
아래와 같이 session-level 환경변수를 설정하는 것도 좋습니다.
|
SQL> set pages 10000 SQL> set long 99999 SQL> set heading off /* column명 등이 표시되는 header가 표시되지 않도록 한다 */ SQL> set linesize 200 SQL> set feedback off /* ".. rows selected" 등의 feedback정보가 표시되지 않도록 한다 */ SQL> col ddl format a200 /* "ddl"을 alias로 사용할 예정이기 때문에 표시될 폭을 설정한다 */ |
위의 같이 출력결과의 format을 설정할 수 있으며,
"dbms_metadata.set_transform_param" 함수를 통해
"dbms_metadata" package가 출력하는 내용을 조정할 수 있습니다.
(1) SQLTERMINATOR - 결과의 마지막에 semicolon(;)을 붙일 지를 결정하는데,
default값은 semicolon을 붙이지 않는 "false"입니다.
"true"로 설정하면 다음과 같이 semicolon이 붙습니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', false); SQL> select dbms_metadata.get_ddl('INDEX', 'PK_EMP', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno")
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); SQL> /
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") ;
SQL> |
(2) PRETTY – 들여쓰기(indent) 및 개행(line feed)를 통해
보다 알아보기 쉽게 표시할 지를 결정하는 parameter입니다.
default값은 "true"이며, indent 및 line feed를 통해 예쁘게 표시되도록 합니다.
"false"로 설정하면 다음과 같이 DDL이 한 줄로 표시됩니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', false); SQL> select dbms_metadata.get_ddl('TABLE', 'DEPT', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create table "scott"."dept" ("deptno" number(2,0), "dname" varchar2(14), "loc" varchar2(13)) ;
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); SQL> /
DDL -------------------------------------------------------------------------------- create table "scott"."dept" ( "deptno" number(2,0), "dname" varchar2(14), "loc" varchar2(13) ) ;
SQL> |
(3) TABLESPACE – 해당 object가 저장된 tablespace정보를 표시할 지를 정하는 parameter입니다.
default값은 "true"이며, tablespace정보가 표시되도록 합니다.
"false"로 설정하면 다음과 같이 tablespace정보가 표시되지 않습니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'TABLESPACE', false); SQL> select dbms_metadata.get_ddl('INDEX', 'PK_EMP', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") pctfree 10 initrans 2 maxtrans 255 storage(initial 16384 next 16384 minextents 1 maxextents 505 pctincrease 50 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default);
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'TABLESPACE', true); SQL> /
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") pctfree 10 initrans 2 maxtrans 255 storage(initial 16384 next 16384 minextents 1 maxextents 505 pctincrease 50 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default) tablespace "users" ;
SQL> |
(4) STORAGE – 해당 object의 storage clause를 표시할 지를 정하는 parameter입니다.
default값은 "true"이며, storage clause가 표시되도록 합니다.
"false"로 설정하면 다음과 같이storage clause가 표시되지 않습니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false); SQL> select dbms_metadata.get_ddl('INDEX', 'PK_EMP', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") pctfree 10 initrans 2 maxtrans 255 ;
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', true); SQL> /
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") pctfree 10 initrans 2 maxtrans 255 storage(initial 16384 next 16384 minextents 1 maxextents 505 pctincrease 50 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default) tablespace "users" ;
SQL> |
※ tablespace정보는 storage clause와 독립적으로 존재할 수 없기 때문에,
"STORAGE" parameter값이 "false"로 설정되면, tablespace정보는
"TABLESPACE" parameter를 "true"로 설정해도 표시되지 않습니다.
(5) SEGMENT_ATTRIBUTES – 해당 object의 segment정보(물리적 속성, storage속성,
tablespace정보, logging여부, 등등)를 표시할 지를 정하는 parameter입니다.
default값은 "true"이며, segment정보가 표시되도록 합니다.
"false"로 설정하면 다음과 같이 segment정보가 표시되지 않습니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false); SQL> select dbms_metadata.get_ddl('INDEX', 'PK_EMP', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") ;
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true); SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false); SQL> /
DDL -------------------------------------------------------------------------------- create unique index "scott"."pk_emp" on "scott"."emp" ("empno") pctfree 10 initrans 2 maxtrans 255 ;
SQL> |
※ tablespace정보도, storage clause도 segment속성과 독립적으로 존재할 수 없기 때문에,
"SEGMENT_ATTRIBUTES" parameter값이 "false"로 설정되면, parameter의 설정과 관계없이
tablespace정보도, storage clause도 표시되지 않습니다.
(6) CONSTRAINTS – non-referential constraints를 표시할 지를 결정하는 parameter입니다.
default값은 "true"이며, constraints정보가 표시됩니다.
"false"로 설정하면 다음과 같이 constraints정보가 표시되지 않습니다.
|
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'CONSTRAINTS', false); SQL> select dbms_metadata.get_ddl('TABLE', 'DEPT', 'SCOTT') ddl from dual;
DDL -------------------------------------------------------------------------------- create table "scott"."dept" ( "deptno" number(2,0), "dname" varchar2(14), "loc" varchar2(13) ) ;
SQL> execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'CONSTRAINTS', true); SQL> /
DDL -------------------------------------------------------------------------------- create table "scott"."dept" ( "deptno" number(2,0), "dname" varchar2(14), "loc" varchar2(13), constraint "pk_dept" primary key ("deptno") enable ) ;
SQL> |
