Reverse engineering object DDL

This is an hands-on to retrieves the creation DDL for the "EMPLOYEE" table
and the dependents(index, constraints etc) associated with the table.
This is known as Reverse engineering object DDL which is very useful in
data migration and understanding the default values that oracle use while
creating the database objects.

This can be achieved with the help of DBMS_METADATA package and GUI tool in TOAD.

1. Create a table and insert data
---------------------------------
SQL> create table employee as(
  2  select * from scott.emp);

Table created.

2. Create index and add constraints to employee table
-----------------------------------------------------
SQL> CREATE UNIQUE INDEX PK_EMPLOYEE ON EMPLOYEE ("EMPNO");

Index created.

SQL> CREATE INDEX MANAGER_IDX ON EMPLOYEE ("MGR");

Index created.

SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE UNIQUE ("EMPNO");

Table altered.

3.Now Let us try to fetch the DDLs using DBMS_METADATA package
--------------------------------------------------------------------------
To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown

note: You may categorize the DDLs and can be spooled in to a file if you want to make a sql script.

SET LONG 2000000
SET PAGESIZE 0

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')
           FROM DUAL;

  CREATE TABLE "SAMPLE"."EMPLOYEE"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMPLOYEE" UNIQUE ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"  ENABLE
 ) SEGMENT CREATION IMMEDIATE
 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"


SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL
          ('INDEX','EMPLOYEE') FROM DUAL;

  CREATE UNIQUE INDEX "SAMPLE"."PK_EMPLOYEE" ON "SAMPLE"."EMPLOYEE" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EXAMPLE"

  CREATE INDEX "SAMPLE"."MANAGER_IDX" ON "SAMPLE"."EMPLOYEE" ("MGR")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EXAMPLE"

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL
           ('CONSTRAINT','EMPLOYEE') FROM DUAL;

  ALTER TABLE "SAMPLE"."EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" UNIQUE ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE


!!!!FEELING LAZY???? THEN TRY TOAD'S "GENERATE SCHEMA SCRIPT" TOOL!!!!!