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

• Use a script
• Use export and unix utilities, ie

  exp file=MY_DUMP.dmp userid=... strings MY_DUMP.dmp | grep '^CREATE TABLE'

• Use import with SHOW=Y
• Use import with INDEXFILE=...

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:

• ASSOCIATION
• AUDIT
• AUDIT_OBJ
• CLUSTER
• COMMENT
• CONSTRAINT
• CONTEXT
• DB_LINK
• DEFAULT_ROLE
• DIMENSION
• DIRECTORY
• FUNCTION
• INDEX
• INDEXTYPE
• JAVA_SOURCE
• LIBRARY
• MATERIALIZED_VIEW
• MATERIALIZED_VIEW_LOG
• OBJECT_GRANT
• OPERATOR
• OUTLINE
• PACKAGE
• PACKAGE_SPEC
• PACKAGE_BODY
• PROCEDURE
• PROFILE
• PROXY
• REF_CONSTRAINT
• ROLE
• ROLE_GRANT
• ROLLBACK_SEGMENT
• SEQUENCE
• SYNONYM
• SYSTEM_GRANT
• TABLE
• TABLESPACE
• TABLESPACE_QUOTA
• TRIGGER
• TRUSTED_DB_LINK
• TYPE
• TYPE_SPEC
• TYPE_BODY
• USER
• VIEW
• XMLSCHEMA

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