Useful commands to fetch DDL statement of Oracle objects

To fetch the DDL of a table:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'table_name') FROM DUAL;

To fetch the DDL of a view:

SELECT DBMS_METADATA.GET_DDL('VIEW', 'view_name') FROM DUAL;

To fetch the DDL of a procedure:

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'procedure_name') FROM DUAL;

To fetch the DDL of a function:

SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'function_name') FROM DUAL;

To fetch the DDL of a package:

SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'package_name') FROM DUAL;

To fetch the DDL of a package body:

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'package_name') FROM DUAL;

To fetch the DDL of a trigger:

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name') FROM DUAL;

To fetch the DDL of a sequence:

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'sequence_name') FROM DUAL;

To fetch the DDL of a synonym:

SELECT DBMS_METADATA.GET_DDL('SYNONYM', 'synonym_name') FROM DUAL;

To fetch the DDL of an index:

SELECT DBMS_METADATA.GET_DDL('INDEX', 'index_name') FROM DUAL;

To fetch the DDL of a user:

SELECT DBMS_METADATA.GET_DDL('USER', 'user_name') FROM DUAL;

To fetch the DDL of a role:

SELECT DBMS_METADATA.GET_DDL('ROLE', 'role_name') FROM DUAL;

To fetch the DDL of a tablespace:

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

To get the definition of the foreign key constraints.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','<table_name>','<schema>') from dual;

To get the System privileges grants for a schema:

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;

To get the Role grant for a schema:

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;

To get the object grants for a schema:

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Building a Scalable and Secure AI Infrastructure in the Cloud - May 12, 2024
• Harnessing the Power of AI in the Cloud for Business Transformation - March 2, 2024
• Building a Resilient Cybersecurity Framework in the Oracle Cloud - January 16, 2024