How to use dbms_metadata.get_ddl to get Complete DDL for database objects

This post describes a simple way to retrieve an object definition, also called the DDL (Data Definition Language) of an object. Retrieving the complete definition (DDL) of a dictionary object (i.e. table, index, view, tablespace, etc.) can be useful in these circumstances:

• Confirm the object definition
• Transfer the object definition to another database
• Resolve a database issue using My Oracle Support to describe the properties of an object

To obtain the definition of an object using the SQL command line (SQL Plus), the built-in package DBMS_METADATA can be used.

Example 1 (single object with various degrees of detail)

Retrieve the definition of the [Table_Name_1] table in schema [Owner_Schema]. Use the following command in SQLPlus, either as user [Owner_Schema] or as SYS:

set heading off
set echo off
set flush off
set pagesize 0
set linesize 9999
set long 200000

select dbms_metadata.get_ddl('TABLE','[Table_Name_1]', '[Owner_Schema]') from dual ;

CREATE TABLE "[Owner_Schema]"."[Table_Name_1]"
( "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_[Table_Name_1]" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
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 "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "[Owner_Schema]"."" ("DEPTNO") 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 "USERS"

When using this text to define a similar object in a different instance, all dependent objects (TABLESPACES, USERs) must already exist.

The first parameter specifies the object type of the object to be generated. Note that this is required since there can be objects of the same name but different object type. Examples are directory objects or Materialized Views.

Examples for object types are:

• TABLE
• INDEX
• VIEW
• TABLESPACE
• MATERIALIZED_VIEW
• MATERIALIZED_VIEW_LOG
• DB_LINK
• DIRECTORY
• SYNONYM
• TRIGGER

There are many more object types for which we can get the DDL, but we have listed most commonly used. The second parameter specifies the name of the object to be generated. In general, a non-privileged user has access only to his own objects as well as public synonyms. To see all object definitions of the database, a user must log in as SYS or be granted the SELECT_CATALOG_ROLE role.

The third parameter optionally specifies the schema of the object – it defaults to the user’s schema. Note that this output includes many definition phrases (for example: TABLESPACE) that may not have been specified at the time the object had been defined. You can hide specific sections of the definition by executing one or more of following command before retrieving the object definition:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'keyword',false);

Use the following keyword to hide the corresponding clause of the definition:

To hide more than one section of a definition, execute the command multiple times with different keywords. To reset display of one of the above sections, use the parameter true instead of false:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'keyword',true);

To make all sections visible again, use the following command:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT',true);

Example 2 (dependent objects)

Find the DDL of all INDEXES defined on table [Table_Name_1] for schema [Owner_Schema]:

Execute the following as user [Owner_Schema]:

select dbms_metadata.get_ddl('INDEX', u.INDEX_NAME) from user_indexes u
where u.table_owner = '[Owner_Schema]' and u.table_name = '[Table_Name_1]' ;

CREATE UNIQUE INDEX "[Owner_Schema]"."PK_[Table_Name_1]" ON "[Owner_Schema]"."[Table_Name_1]" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
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 "USERS"

CREATE INDEX "[Owner_Schema]"."" ON "[Owner_Schema]"."[Table_Name_1]" ("ENAME")
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 "USERS"

Alternative:

select dbms_metadata.get_dependent_ddl('INDEX','[Table_Name_1]', '[Owner_Schema]') from dual;

Note that you can also hide specific sections of the output as described above with the SET_TRANSFORM_PARAM routine.

Important – As you can see above, the definitions are generated without a SQL terminator, that is, a semicolon (;) or backslash (\). If you would like to haves such a terminator added to each definition, use the following command first:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);

Example 3 (tablespaces)

To review the definition of the tablespace USERS. execute the following as user SYS:

select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

CREATE TABLESPACE "USERS" DATAFILE
'[Datafiles_Path]/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

Example 4 (grants)

1. To retrieve all object grants for a specific object, you may use the following command:

select dbms_metadata.get_dependent_ddl('OBJECT_GRANT', '[object name]', '[schema]') FROM DUAL; 

2. To retrieve all system grants to a user (for example, [Owner_Schema]):

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','[Owner_Schema]') from dual;

3. If you expect multiple objects to be displayed, it is a good idea to enable SQL terminator display first:

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);