There are plenty of times when you’ll need to grab the ddl for individual objects, and you can do it easily enough in Toad or SQL Developer, but what if those tools aren’t available?  You can use the data pump utility to export and then import definitions, but not before 10g, and there’s an easier way if you’re only looking for a couple of objects.  The dbms_metadata package has a function called get_ddl that can pull the ddl for a specified object (there’s a similar get_xml function too, more info here).   Just specify the object type, the object name, and the schema name, and the function returns the ddl.  Here’s a little example:

-I’ll create a simple table to extract the definition of:

SQL> CREATE TABLE PEOPLE (NAME VARCHAR2(30), AGE NUMBER(2), constraint people_pk PRIMARY KEY (age));

Table created.

1. Be sure to set the pagesize to 0 and set long to an arbitrarily large value

SQL> set pagesize 0
SQL> set long 150000

2. You’ll probably want to spool the output

SQL> spool people.sql

3. Make the get_ddl function call, select it from dual since it returns a value

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

CREATE TABLE "CHARLOTTE"."PEOPLE"
( "NAME" VARCHAR2(30),
"AGE" NUMBER(2,0),
CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("AGE")
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 "CHARLOTTE_DATA" 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 "CHARLOTTE_DATA"

 

4. Make any necessary edits  In most cases you can get rid of all the storage information (cut this output off after the constraint definition).  I created this primary key to demonstrate that constraints will be a part of the table definition generated, however you may want to edit them out.   As a best practice indexes should be created on constrained columns and then the constraint should be applied over the top using the existing index.  If you run this generated ddl with the constraint defined, the new table/index structure may be different from the original table/index.  You can make a similar get_ddl call to generate the necessary indexes and constraints, or just create them manually.

I don’t use this function often, but it can come in very handy if you just need something quickly, and new DBAs should always be weary of being too reliant on GUI tools such as Toad or even OEM.

Leave a comment to let me know if you run into any problems with this.