Oracle: Get DDL for Constraints using dbms_metadata.get_ddl() Function

Sometimes there is a requirement to get the DDL statement for constraints only in Oracle. To do this you can use the dbms_metadata.get_ddl() function. Below are the examples:

Get DDL for Constraints using dbms_metadata.get_ddl() Function in Oracle

Below is an example dbms_metadata.get_data() function to get the DDL for all the constraints for a particular table:

Example-1

Select
   c.constraint_name,
   dbms_metadata.get_ddl('CONSTRAINT', c.constraint_name) ddl_const
From
   user_constraints c
Where
   table_name = 'MY_TABLE';

To get the DDL for a specific constraint, use the following query:

Example-2

Select
   dbms_metadata.get_ddl('CONSTRAINT', 'MY_CONSTRAINT')   ddl_const
   From
dual;

The above queries will return the DDL statements with the Storage Clause. If you don’t require the DDL statement with the Storage Clause, then execute the below statement before running above commands:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);