How to Get DDL for Oracle Objects

Learn how to retrieve DDL (Data Definition Language) statements for Oracle objects like tables, views, and indexes. This guide provides step-by-step methods to extract the DDL in Oracle for efficient database management.

Output Formatting

-- Ensure long output is not truncated
SET LONG 9999;

-- Set environment to handle large outputs
SET LONG 9999;
SET PAGESIZE 5000;
SET LINESIZE 200;

Table DDL

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

View DDL

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

Procedure DDL

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

Function DDL

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

Package DDL

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

Package Body DDL

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

Trigger DDL

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

Sequence DDL

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

Synonym DDL

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

Index DDL

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

User DDL

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

Role DDL

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

Tablespace DDL

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'tablespace_name') FROM DUAL;

Foreign Key Constraints DDL

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', '<table_name>', '<schema>') FROM DUAL;

To Get System Privileges Granted to a Schema

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '<schema>') FROM DUAL;

To Get Role Grants for a Schema

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '<schema>') FROM DUAL;

To Get Object Grants for a Schema

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '<schema>') FROM DUAL;

To Get the DDL of All Objects for a Specific Schema

SPOOL all_schema_objects.sql
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE_BODY', 'TRIGGER', 'INDEX', 'SEQUENCE', 'SYNONYM')
AND OWNER = '<owner_name>'; -- Replace with the schema owner
SPOOL OFF;

To Get the DDL of All Objects Owned by the Current User

SPOOL user_objects.sql
SELECT DBMS_METADATA.GET_DDL(object_type, object_name)
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE_BODY', 'TRIGGER', 'INDEX', 'SEQUENCE', 'SYNONYM');
SPOOL OFF;

To Get the DDL of All Users

SPOOL all_users.sql
SELECT DBMS_METADATA.GET_DDL('USER', username)
FROM DBA_USERS;
SPOOL OFF;

To Get the DDL for All Tablespaces

SPOOL all_tablespaces.sql
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name)
FROM DBA_TABLESPACES;
SPOOL OFF;

To Get the DDL for All Roles

SPOOL all_roles.sql
SELECT DBMS_METADATA.GET_DDL('ROLE', role)
FROM DBA_ROLES;
SPOOL OFF;

To Get Privileges Granted to a Specific User

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '<user_name>') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '<user_name>') FROM DUAL;

To Get the DDL Including Constraints and Dependent Objects for a Table

SPOOL table_and_constraints.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', '<table_name>', '<owner>') FROM DUAL;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', '<table_name>', '<owner>') FROM DUAL;
SPOOL OFF;

To Get the DDL for All Indexes of a Schema

SPOOL schema_indexes.sql
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, table_owner)
FROM ALL_INDEXES
WHERE TABLE_OWNER = '<schema_name>';
SPOOL OFF;

To Get the DDL of All Grants for a Schema

SPOOL schema_grants.sql
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '<schema_name>') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '<schema_name>') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '<schema_name>') FROM DUAL;
SPOOL OFF;

To Get the DDL for DBMS Scheduler Jobs for a Schema

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', job_name, owner)
FROM DBA_SCHEDULER_JOBS
WHERE OWNER = '<schema_name>'; -- Replace <schema_name> with the relevant schema

To Get the DDL for All DBMS Scheduler Jobs of the Current User

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', job_name)
FROM USER_SCHEDULER_JOBS;

To Get the DDL for DBMS Jobs (Legacy DBMS_JOBS)

SELECT DBMS_METADATA.GET_DDL('JOB', job_name, owner)
FROM DBA_JOBS
WHERE OWNER = '<schema_name>'; -- Replace <schema_name> with the relevant schema

To Get the DDL for All DBMS Jobs of the Current User

SELECT DBMS_METADATA.GET_DDL('JOB', job_name)
FROM USER_JOBS;

To Get the Details of Scheduler Job Arguments

SELECT *
FROM DBA_SCHEDULER_JOB_ARGS
WHERE OWNER = '<schema_name>'; -- Replace <schema_name> with the relevant schema

To Get the All Scheduler Job DDL for a Schema

SPOOL scheduler_jobs.sql
SELECT DBMS_METADATA.GET_DDL('PROCOBJ', job_name, owner)
FROM DBA_SCHEDULER_JOBS
WHERE OWNER = '<schema_name>'; -- Replace <schema_name> with the relevant schema
SPOOL OFF;