GET DDL OF ORACLE OBJECTS
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",