ADVERTISEMENT

Extract DDL for Oracle Objects

This guide shows how to extract the CREATE statements (DDL) for common Oracle objects such as TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, DATABASE LINK, INDEX, SEQUENCE, SYNONYM, JOB, LOB, and MATERIALIZED VIEW, with proper formatting using built-in Oracle packages.

✅ DDL for Any Object Using DBMS_METADATA.GET_DDL

🔄 Replace 'HR' and 'TABLE_NAME' with your schema and object name.

SET LONG 100000;
SET PAGESIZE 0;
SET LINESIZE 200;
SET FEEDBACK OFF;
SET ECHO OFF;

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

📌 Note: You can change 'TABLE' to any of the following:

  • 'VIEW'
  • 'PROCEDURE'
  • 'FUNCTION'
  • 'PACKAGE'
  • 'PACKAGE BODY'
  • 'TRIGGER'
  • 'DATABASE LINK'
  • 'INDEX'
  • 'SEQUENCE'
  • 'SYNONYM'
  • 'MATERIALIZED VIEW'

✅ DDL for JOBs (DBMS_JOB)

Oracle doesn’t support direct DDL via DBMS_METADATA for jobs. Use this query to inspect scheduled jobs.

SELECT * FROM DBA_JOBS WHERE SCHEMA_USER = 'HR';

📌 Tip: For DBMS_SCHEDULER jobs, use:

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', 'JOB_NAME', 'HR') FROM DUAL;

✅ DDL for LOBs (Inside Tables)

LOBs are part of tables. Use this to extract LOB info as part of table DDL:

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

📌 Note: The output includes LOB STORAGE, TABLESPACE, and CHUNK settings if they exist.

📤 Exporting All Object DDLs from a Schema

To export DDLs for all objects of a specific type:

SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM dba_objects
WHERE owner = 'HR'
AND object_type IN (
  'TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE',
  'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE',
  'SYNONYM', 'MATERIALIZED VIEW'
);

📌 Note: Filter by object_type as needed. JOB and LOB need special handling.

🧾 Final Tip

  • Always run this in SQL*Plus or SQL Developer for proper formatting.
  • Use SET LONG to avoid truncated DDL output.
  • Wrap output in a spool file to save for backup or migration purposes.

ADVERTISEMENT