How To Extract DDL

BACKGROUND & OVERVIEW

In situations where a GUI tool, e.g. OEM or TOAD, is not available, the database function DBMS_METADATA.GET_DDL may prove useful. The function has the following characteristics:

FUNCTION GET_DDL RETURNS CLOB

Argument_Name    Type      In/Out Default?
------------------------------------------
OBJECT_TYPE      VARCHAR2  IN
NAME             VARCHAR2  IN
SCHEMA           VARCHAR2  IN     DEFAULT
VERSION          VARCHAR2  IN     DEFAULT
MODEL            VARCHAR2  IN     DEFAULT
TRANSFORM        VARCHAR2  IN     DEFAULT

EXAMPLES OF USE

  • Example 1: Extracting DDL for TAB_A from the USER_X schema.
SET LONG 2000
 
SELECT
dbms_metadata.get_ddl('TABLE','TAB_A','USER_X')
FROM DUAL;
 
CREATE TABLE "USER_X"."TAB_A"
("COL1" NUMBER(*,0) NOT NULL ENABLE,
"COL2" NUMBER(*,0) NOT NULL ENABLE,
"COL3" NUMBER(*,0) NOT NULL ENABLE,
"COL4" VARCHAR2(3) NOT NULL ENABLE,
CONSTRAINT "PK_TAB_A"
PRIMARY KEY ("COL1", "COL2")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 40960 NEXT 1048576 
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "INDEX_TSP" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 40960 NEXT 1048576 
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "TABLE_TSP"
  • Example 2: Extracting DDL for IDX_B from the USER_Y schema.
SET LONG 2000
 
SELECT
dbms_metadata.get_ddl('INDEX','IDX_B','USER_Y')
FROM DUAL;
 
CREATE UNIQUE INDEX "USER_Y"."IDX_B" 
ON "USER_Y"."TAB_B" ("COL1")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 1064960 NEXT 1048576 
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "INDEX_TSP"
  • Note: The schema where the object resides needs to be specified in the function, otherwise the function will default to the schema from where it is executing. If an object of the requested name does not exist, an error occurs.
© copyright 2001-2014 ABCdba.com | all rights reserved