Oracle DDL取得(DBMS_METADATA.GET_DDL)

OracleのDDLコマンドを自動生成できる

Oracle データベースでは、既存オブジェクト再作成用の SQL (DDL) を
作成するファンクションが提供されている。

実はコレ、用途によっては利用価値が高いので、目から鱗状態の人も少なくないはず。
作成するには、DBMS_METADATA.GET_DDL ファンクションを使用する。

既存オブジェクトの Create文が欲しいとき

テーブルやビューなどのオブジェクトから定義を出力する DBMS_METADATA.GET_DDL。
既存オブジェクト再作成 SQL を出力できるのは、とっても便利だ。

出力フォーマットは CLOB のため、set pages と set long の設定が必要。

SQL コマンド

select dbms_metadata.get_ddl('(1)','(2)','(3)') from dual;

(1) オブジェクト種類：TABLESPACE、TABLE、INDEX、VIEW、SYNONYM、PACKAGE、PROCEDURE など
(2) オブジェクト名：対象のオブジェクト名を指定
(3) スキーマ名：省略時は現在のスキーマとなる

例：SYSTEM 表領域の DDL 出力

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;

  CREATE TABLESPACE "SYSTEM" DATAFILE
  '/share/oradata/ORCL/system01.dbf' SIZE 1048576000
  AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL

例：HOGE スキーマの TEST テーブル定義出力

SQL> set long 10000
SQL> set pages 0
SQL> select dbms_metadata.get_ddl('TABLE','TEST','HOGE') from dual;

  CREATE TABLE "HOGE"."TEST"
   (    "COL1" NUMBER,
        "COL2" CHAR(10),
        "COL3" DATE
   ) 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 "TESTTBS"

このスクリプトを控えておけば、オブジェクトの再作成が容易になるので、
是非覚えておくべき Tips のひとつ。