Use DBMS_METADATA to write DDL

Tweet

On page 353 of Expert Oracle Database Architecture, Tom Kyte writes about a really cool trick. Consider this simple create table statement:

HR@XE> create table myTable (
  2  id number primary key,
  3  name varchar2(100),
  4  doc_content clob,
  5  creation_date date default sysdate)
  6  /

Table created.

Now, if I want to add and customize the storage clause in this DDL stattement, unless I have the syntax of the create table memorized (which I don’t), I need to consult with the documentation. The documentation of many DDL commands spans tens of pages, so to save some time reading through the docs, I can tell Oracle to produce the verbose version of the above create table:

HR@XE> select dbms_metadata.get_ddl('TABLE', 'MYTABLE') myDDL
  2  from dual
  3  /


MYDDL
--------------------------------------------------------------

  CREATE TABLE "HR"."MYTABLE"
   (    "ID" NUMBER,
        "NAME" VARCHAR2(100),
        "DOC_CONTENT" CLOB,
        "CREATION_DATE" DATE DEFAULT sysdate,
         PRIMARY KEY ("ID")
  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_POOL DEFAULT)
  TABLESPACE "USERS"  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"
   LOB ("DOC_CONTENT") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW 
  CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 
  MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT))

Using SQLinForm, I can beautify the full DDL above, the result:

 CREATE 
    TABLE "HR"."MYTABLE" 
    ( 
        "ID" NUMBER, 
        "NAME" VARCHAR2(100), 
        "DOC_CONTENT" CLOB, 
        "CREATION_DATE" DATE DEFAULT sysdate, 
        PRIMARY KEY ("ID") 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_POOL
        DEFAULT) TABLESPACE "USERS" 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" LOB 
    (
        "DOC_CONTENT"
    ) 
    STORE AS 
    ( 
        TABLESPACE "USERS" ENABLE STORAGE IN ROW 
        CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING 
        STORAGE(INITIAL 65536 NEXT 1048576 
        MINEXTENTS 1 MAXEXTENTS 2147483645 
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
        1 BUFFER_POOL DEFAULT)
    )

Now, I can see all the options available to me and change them as needed. Sweet.

Related articles:

• Two Quick and Simple Tips That Will Help You Write Better PL/SQL
• CURRVAL and NEXTVAL
• Oracle Trace Analyzer is TKPROF on Steroids

Filed in Oracle, Tips on 14 Dec 05 | Tags: dbms_metadata, ddl, sql

---