19 | 08 | 2017
Latest Articles
Popular Articles

Administration

Get DDL using DBMS_METADATA

User Rating:  / 0
PoorBest 

dbms_metadata.get_ddl in order to get .. the ddl

Since versions 9i Oracle has introduced the package dbms_metadata. I very often use the get_ddl procedure in order to retrieve the ddl statement to be able to recreate the object, in order to know how the object has been created. Easy to use Let me show you how I am proceeding

SQL> set pagesize0

SQL> set long 20000

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

CREATE TABLE "MY_UTF"."MY_TABLE_11"
( "PK_MY_TABLE_11" NUMBER,"FK_MY_TABLE_10" NUMBER,"NAME" VARCHAR2(20 CHAR),
CONSTRAINT "PK_MY_TABLE_11" PRIMARY KEY ("PK_MY_TABLE_11")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_PO
OL DEFAULT) TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_MY_TABLE_1110" FOREIGN KEY ("FK_MY_TABLE_10")
REFERENCES "MY_UTF"."MY_TABLE_10" ("PK_MY_TABLE_10") 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"

If I wanna have the same without storage attributes, I proceed in this way

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

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

CREATE TABLE "MY_UTF"."MY_TABLE_11"
( "PK_MY_TABLE_11" NUMBER,"FK_MY_TABLE_10" NUMBER,"NAME" VARCHAR2(20 CHAR),
CONSTRAINT "PK_MY_TABLE_11" PRIMARY KEY ("PK_MY_TABLE_11")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_MY_TABLE_1110" FOREIGN KEY ("FK_MY_TABLE_10")
REFERENCES "MY_UTF"."MY_TABLE_10" ("PK_MY_TABLE_10") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"

SQL> select dbms_metadata.get_ddl('INDEX','PK_MY_TABLE_11') from dual;

CREATE UNIQUE INDEX "MY_UTF"."PK_MY_TABLE_11" ON "MY_UTF"."MY_TABLE_11" ("PK_MY_TABLE_11")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS"

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

CREATE USER "MY_UTF" IDENTIFIED BY VALUES 'C6D504D40AD3ADE8'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

SQL> select trigger_name from user_triggers;
TRG_T_IDENTITY
MY_TRIGGER
ENCRYPTED_DATA_BIUR_TRG

SQL> select dbms_metadata.get_ddl('TRIGGER','MY_TRIGGER') from dual;

CREATE OR REPLACE TRIGGER "MY_UTF"."MY_TRIGGER"
before insert on my_table_data
for each row
declare
var_trigger number;
begin
var_trigger := :new.col1;
my_proc (var_trigger);
end;

ALTER TRIGGER "MY_UTF"."MY_TRIGGER" ENABLE