How to get Oracle Database Object DDL

<<Back to DB Administration Main Page

How to get the DDL of an Object from Oracle Database using  dbms_metadata Package

Syntax
set long 32000
set longchunk 32000
set lines 200
set pages 0
set trimspool on
spool /tmp/meta_data.lst
select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') from dual
/
spool off;

Finding the Table DDL 

Example
set long 32000
set longchunk 32000
set lines 200
set pages 0
set trimspool on
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEE','TEST') from dual;

where 
'TABLE' => Object Type
'EMPLOYEE' => Name of the Table for which DDL is needed
'TEST' => Owner of the Employee Table in Database

Finding the Index DDL 
Just  change the object_type in above command  
Example:
set long 32000
set longchunk 32000
set lines 200
set pages 0
set trimspool on
SQL> select dbms_metadata.get_ddl('INDEX','IDX1','TEST') from dual

where

'INDEX'=> Object Type

'IDEX1' => Name of the INDEX for which DDL is needed

'TEST' => Owner of the IDX1 Index 

How to Find the DDL of a dependent Objects in Oracle Database

To find the DDL of a dependent object you can use dbms_metadata.get_dependent_ddl function

Syntax
set long 32000
set longchunk 32000
set lines 200
set pages 0
set trimspool on
SQL> select dbms_metadata.get_dependent_ddl('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') from dual;

Example:
to find all the dependent indexes for table EMPLOYEE in TEST schema use below command
set long 32000
set longchunk 32000
set lines 200
set pages 0
set trimspool on
SQL> select dbms_metadata.get_ddl('INDEX','EMPLOYEE','TEST') from dual;

where

'INDEX'=> Object Type

'EMPLOYEE' => Name of the Table for which dependent Index DDL is needed

'TEST' => Owner of the EMPLOYEE Table