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.