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
exp file=MY_DUMP.dmp userid=... strings MY_DUMP.dmp | grep '^CREATE TABLE'
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.