Obtaining DDL


It seems a simple enough task to do. You've got a table in the database and you would like to see the DDL that was used to create that table. Or any database object for that matter. Unfortunately, its a difficult thing to achieve. Lets look at the some of the methods typically used to obtain DDL from the database

The real solution is now here...

Enter version 9 with the new DBMS_METADATA package. A simple call to the GET_DDL function within this package returns the DDL as a CLOB

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')
  2  from dual;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        ...
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(...) TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING STORAGE(...) TABLESPACE "USERS"

Its that simple. There are also a number of transformation that you make to the DDL returned. For example, if you wanted to omit the storage parameters, you need simply set the appropriate transformation variable:

begin 
  DBMS_METADATA.SET_TRANSFORM_PARAM(
     DBMS_METADATA.SESSION_TRANSFORM,
     'STORAGE',
     false);
end;

And the DDL is not limited to just tables. You can get the DDL for just about anything in the database. For example, the user definitions are easily retrieved:

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

CREATE USER "SCOTT" 
IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

The different types of DDL you can retrieve are listed below:

If you manipulate or require DDL on a regular basis, then the DBMS_METADATA is a god-send.