To get the script for creating an object, specify the object type and the object name from dba_objects

Table DDL

kIsH@x3z6zx9<^>select DBMS_METADATA.GET_DDL('TABLE','XTBL') from dual;

DBMS_METADATA.GET_DDL('TABLE','XTBL')
--------------------------------------------------------------------------------

  CREATE TABLE "KISH"."XTBL"
   (    "NAME" VARCHAR2(100),
        "XID" NUMBER(20,0),


Elapsed: 00:00:01.46

Index DDL

kIsH@x3z6zx9<^>select DBMS_METADATA.GET_DDL('INDEX','XPK_XTBL1') from dual;

DBMS_METADATA.GET_DDL('INDEX','XPK_XTBL1')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "KISH"."XPK_XTBL1" ON "KISH"."MVSAL" ("XID")
  PCTFREE 1

MVIEW DDL

kIsH@x3z6zx9<^>select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MVSAL') from dual;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MVSAL')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "KISH"."MVSAL" ("NAME", "XID", "CITY", "COUNTRY", "D


Elapsed: 00:00:07.86

To get the DDL for objects from different schema. Provide the schema name at the last field.

kIsH@Xhydra<>select DBMS_METADATA.GET_DDL('TABLE','XTBL','KISH') from dual;

DBMS_METADATA.GET_DDL('TABLE','XTBL','KISH')
--------------------------------------------------------------------------------

  CREATE TABLE "KISH"."XTBL"
   (    "NAME" VARCHAR2(100),
        "XID" NUMBER(20,0),

View DDL

kIsH@Xhydra<>select DBMS_METADATA.GET_DDL('VIEW','V') from dual;

DBMS_METADATA.GET_DDL('VIEW','V')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."V" ("OWNER", "VIEW_NAME",