DBA Tools

    A lot of DBAs are extracting DDL (table create) script with Toad and PL/SQL Developer utilities. But a lot of database are running on Linux or Unix hosts, and connections directly from desktop are not allowed, then it seems to be a hard work. So Oracle introduced DBMS_METADATA package in 9i version, but I don't feel good with this package, after we did manually database upgrade, this package becomes invalid in some of our databases. And I am finding another way to do it.

    In my DBDiff program, I got the DDL script via exp and imp utilities. Just take the following steps. First step is to export the table without any rows. Second step is import it with "SHOW=YES" and "INDEXFILE=logfile" option. The last step is process the log file generated by the imp utility. I spent few hours to package these functions together into a new free utility -- GetDDL. You need to install Oracle client software before you start to use it, and make sure the exp and imp utilities work correctly.

Usage: getddl table=... [user=...]
(c) Copyright Lou Fangxin, 2008, all rights reserved.

    If you ommit the "USER" option, it will connect as sys user. The option "TABLE" specify the table name. Check the following example, I am getting the DDL script of an IOT table.

C:\>getddl table=sh2.t_iottest
CREATE TABLE "T_IOTTEST" ("COL1" NUMBER NOT NULL ENABLE,
  PRIMARY KEY ("COL1") ENABLE)
  ORGANIZATION INDEX PCTFREE 10
  STORAGE ( FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) PCTTHRESHOLD 50 ;

    The triggers will not be generated, if there are domain indexes on it, the DDL to create the domain index may not 100% correct. And no grant statement will be generated. And I remove the tablespace clause, initial and next clause are also removed. Just taste it.