DBMS_METADATA

Use DBMS_METADATA package to generate the syntax of any object in database:

For example to get the definition of DEPT table in SCOTT schema you can use this command:

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Here's an example from Apps:

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual

*

ERROR at line 1:

ORA-24813: cannot send or receive an unsupported LOB

You'll get the above error if you are trying to use this command when your ORACLE_HOME is the 8.0.6 ORACLE_HOME.

Login through the 10.2.0 ORACLE_HOME

SQL> select dbms_metadata.get_ddl('TABLE','FND_NODES','APPLSYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')

----------------------------------------------------------------

  CREATE TABLE "APPLSYS"."FND_NODES"

   (    "NODE_NAME" VARCHAR2(30) NOT NULL EN

You need to set long to 2000 or higher to see the full syntax:

SQL> set long2000

SQL> /

DBMS_METADATA.GET_DDL('TABLE','FND_NODES','APPLSYS')

--------------------------------------------------------------------

  CREATE TABLE "APPLSYS"."FND_NODES"

   (    "NODE_NAME" VARCHAR2(30) NOT NULL ENABLE,

        "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,

        "LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE,

        "CREATION_DATE" DATE NOT NULL ENABLE,

        "CREATED_BY" NUMBER(15,0) NOT NULL ENABLE,

        "LAST_UPDATE_LOGIN" NUMBER(15,0) NOT NULL ENABLE,

        "PLATFORM_CODE" VARCHAR2(30) NOT NULL ENABLE,

        "DESCRIPTION" VARCHAR2(240),

        "BASEPATH" VARCHAR2(20),

        "SUPPORT_CP" VARCHAR2(1),

        "SUPPORT_FORMS" VARCHAR2(1),

        "SUPPORT_WEB" VARCHAR2(1),

        "SUPPORT_ADMIN" VARCHAR2(1),

        "STATUS" VARCHAR2(1),

        "PING_RESPONSE" VARCHAR2(2000),

        "LAST_MONITORED_TIME" DATE,

        "NODE_MODE" VARCHAR2(1),

        "NODE_ID" NUMBER,

        "SERVER_ID" VARCHAR2(64),

        "SERVER_ADDRESS" VARCHAR2(30),

        "HOST" VARCHAR2(256),

        "DOMAIN" VARCHAR2(256),

        "WEBHOST" VARCHAR2(256),

        "VIRTUAL_IP" VARCHAR2(256),

        "SUPPORT_DB" VARCHAR2(1),

        "APPLTOP_ID" RAW(16)

   ) PCTFREE 5 PCTUSED 80 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGI

NG

  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)

  TABLESPACE "APPS_TS_TX_DATA"