Menampilkan DDL Statement

Ada kalanya kita perlu mencari tahu DDL statement yang digunakan untuk membuat Index tertentu di database Oracle. Berikut adalah contoh perintah SQL yang bisa dipakai untuk menampilkan DDL dari sebuah index dengan nama “ORA3_IDX_TDMSWITCH” pada sebuah tabel bernama “TDM_SWITCH” :

SQL> set lines 200
SQL> col output format a200
SQL> select dbms_metadata.get_ddl('INDEX', 'ORA3_IDX_TDMSWITCH','ORAUSER_TREG3') as output from dual;

  CREATE INDEX "ORAUSER_TREG3"."ORA3_IDX_TDMSWITCH" ON "ORAUSER_TREG3"."TDM_SWITCH" ("SWITCH_NAME", "SWITCH_RACK", "SWITCH_SHELF", "SWITCH_CARD")
  PCTFREE 10 INITRANS 50 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ORAUSER_TREG3"


SQL>

Perintah dbms_metadata.get_ddl tersebut bisa juga digunakan untuk menampilkan perintah SQL untuk membuat tabel :

SQL> select dbms_metadata.get_ddl('TABLE', 'TDM_SWITCH', 'ORAUSER_TREG3') as output from dual;

  CREATE TABLE "ORAUSER_TREG3"."TDM_SWITCH"
   (    "SWITCH_NAME" VARCHAR2(40),
    "SWITCH_RACK" VARCHAR2(10),
    "SWITCH_SHELF" VARCHAR2(10),
    "SWITCH_CARD" VARCHAR2(10),
    "SWITCH_PORT" VARCHAR2(15),
    "TELEPHONENUMBER" VARCHAR2(30),
    "DATE_MIGRATE" DATE,
    "STATUS" VARCHAR2(256),
    "UPDATED" NUMBER(1,0),
    "KODE" VARCHAR2(6),
    "MIGRATION_USER" VARCHAR2(255)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 50 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ORAUSER_TREG3"


SQL>

Bahkan perintah yang sama bisa digunakan untuk menampilkan definisi dari Oracle Chain scheduler.

SQL> select dbms_metadata.get_ddl('PROCOBJ', 'BASICENTITIES', 'ORAUSER_TREG3') as output from dual;


BEGIN
dbms_scheduler.create_chain('"BASICENTITIES"', evaluation_interval=>NULL, comments=>NULL
, rule_set_name=>'"SCHED_RULESET$1"   '
);
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"TNBLOCK"', program_name=>'"PROG_TNBLOCK"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"VPIVCI0"', program_name=>'"PROG_VPIVCI0"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"ACTDEV_NETWORKLOCATION0"', program_name=>'"PROG_ACTDEV_NETWORKLOCATION0"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"VLAN_SERVICETYPE0"', program_name=>'"PROG_VLAN_SERVICETYPE0"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"MSANSSMAPPING0"', program_name=>'"PROG_MSANSSMAPPING0"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"IPADDRESS"', program_name=>'"PROG_IPADDRESS"');
dbms_scheduler.define_chain_step('"BASICENTITIES"', step_name=>'"PARTY0"', program_name=>'"PROG_PARTY0"');
dbms_scheduler.enable('"BASICENTITIES"');
COMMIT;
END;


SQL>

Perintah dbms_metadata.get_ddl cukup berguna untuk keperluan backup script SQL yang sudah ada di dalam database Oracle.