DBMS_METADATA - Extract Oracle DDL & Object Definitions

DBMS_METADATA - Extract Oracle DDL & Object Definitions

DBMS_METADATA is Oracle’s programmatic interface for extracting the DDL definitions of database objects. It retrieves the same metadata that Oracle’s data dictionary stores and returns it as formatted SQL, XML, or structured data. DBAs use it for schema migrations, documentation, auditing, change tracking, and building automated deployment scripts.

Overview

Why Use DBMS_METADATA

The package provides several advantages over ad hoc DDL generation:

• Complete and accurate. Oracle generates the DDL itself, so the output matches exactly what was created — including storage clauses, constraints, and grants.
• Controllable output. Transform parameters let you strip storage clauses, tablespace references, or segment attributes to produce portable scripts.
• Programmatic access. You can iterate over an entire schema or object type without interactive tooling.
• Dependency-aware. GET_DEPENDENT_DDL retrieves grants, constraints, and indexes that depend on a table in a single call.

Object Types Reference

-- Common object type strings for GET_DDL
-- Tables and views
'TABLE', 'VIEW', 'MATERIALIZED_VIEW', 'MATERIALIZED_VIEW_LOG'

-- Code objects
'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE_BODY',
'TRIGGER', 'TYPE', 'TYPE_BODY', 'JAVA_SOURCE'

-- Schema objects
'SEQUENCE', 'SYNONYM', 'PUBLIC_SYNONYM', 'DATABASE_LINK'

-- Storage objects
'INDEX', 'CLUSTER', 'TABLESPACE'

-- Security objects
'USER', 'ROLE', 'SYSTEM_GRANT', 'OBJECT_GRANT', 'ROLE_GRANT', 'DEFAULT_ROLE'

-- Constraints (use GET_DEPENDENT_DDL)
'CONSTRAINT', 'REF_CONSTRAINT'

-- Full schema (use GET_DDL with schema-level fetch)
'SCHEMA_EXPORT'

GET_DDL

The primary function. Returns the DDL for a single named object.

-- Syntax
DBMS_METADATA.GET_DDL(
  object_type IN VARCHAR2,
  name        IN VARCHAR2,
  schema      IN VARCHAR2 := NULL,  -- defaults to current user
  version     IN VARCHAR2 := 'COMPATIBLE',
  model       IN VARCHAR2 := 'ORACLE',
  transform   IN VARCHAR2 := 'DDL'
) RETURN CLOB;

Basic GET_DDL Examples

-- Extract table DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR')
FROM dual;

-- Extract an index
SELECT DBMS_METADATA.GET_DDL('INDEX', 'EMP_NAME_IDX', 'HR')
FROM dual;

-- Extract a view
SELECT DBMS_METADATA.GET_DDL('VIEW', 'EMP_DETAILS_VIEW', 'HR')
FROM dual;

-- Extract a sequence
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'EMP_SEQ', 'HR')
FROM dual;

-- Extract a stored procedure
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'CALCULATE_BONUS', 'HR')
FROM dual;

-- Extract a user account (run as DBA)
SELECT DBMS_METADATA.GET_DDL('USER', 'HR')
FROM dual;

-- Extract a role
SELECT DBMS_METADATA.GET_DDL('ROLE', 'APP_READ_ROLE')
FROM dual;

-- Extract a tablespace definition
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'USERS')
FROM dual;

-- Extract a public synonym
SELECT DBMS_METADATA.GET_DDL('PUBLIC_SYNONYM', 'DUAL')
FROM dual;

-- Print cleanly using SET LONG
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SELECT DBMS_METADATA.GET_DDL('TABLE', 'ORDERS', 'SHOP') FROM dual;

Extract DDL for All Objects of a Type in a Schema

-- Generate DDL for every table in a schema
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner)
FROM dba_tables
WHERE owner = 'HR'
ORDER BY table_name;

-- Generate DDL for every index in a schema
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner)
FROM dba_indexes
WHERE owner = 'HR'
  AND index_type NOT IN ('LOB', 'CLUSTER')  -- skip system-managed indexes
ORDER BY table_name, index_name;

-- Generate DDL for all views
SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, owner)
FROM dba_views
WHERE owner = 'HR'
ORDER BY view_name;

-- Generate DDL for all sequences
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, sequence_owner)
FROM dba_sequences
WHERE sequence_owner = 'HR'
ORDER BY sequence_name;

GET_DEPENDENT_DDL

Retrieves DDL for objects that depend on a base object — primarily grants, constraints, and indexes that reference a specific table.

-- Syntax
DBMS_METADATA.GET_DEPENDENT_DDL(
  object_type        IN VARCHAR2,
  base_object_name   IN VARCHAR2,
  base_object_schema IN VARCHAR2 := NULL
) RETURN CLOB;

Examples

-- Get all grants on a table
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMPLOYEES', 'HR')
FROM dual;

-- Get all constraints for a table (PK, UK, FK, CHECK)
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'EMPLOYEES', 'HR')
FROM dual;

-- Get referential constraints (foreign keys pointing TO this table)
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', 'EMPLOYEES', 'HR')
FROM dual;

-- Get all indexes (including those not returned by GET_DDL for the table)
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES', 'HR')
FROM dual;

-- Get triggers on a table
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER', 'EMPLOYEES', 'HR')
FROM dual;

-- Comprehensive table DDL: table + constraints + indexes + grants
DECLARE
  l_ddl CLOB;

  PROCEDURE print_ddl(p_label IN VARCHAR2, p_ddl IN CLOB) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('-- ===== ' || p_label || ' =====');
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(p_ddl, 32000, 1));
  END;

BEGIN
  print_ddl('TABLE', DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR'));

  BEGIN
    print_ddl('CONSTRAINTS',
      DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT', 'EMPLOYEES', 'HR'));
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    print_ddl('INDEXES',
      DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES', 'HR'));
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    print_ddl('GRANTS',
      DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMPLOYEES', 'HR'));
  EXCEPTION WHEN OTHERS THEN NULL; END;
END;
/

SET_TRANSFORM_PARAM

Controls how the returned DDL is formatted and what clauses are included. Must be set before calling GET_DDL or applied to a handle-based session.

-- Session-level transform (affects all subsequent GET_DDL calls in session)
DBMS_METADATA.SET_TRANSFORM_PARAM(
  transform_handle IN NUMBER,   -- use DBMS_METADATA.SESSION_TRANSFORM for session-level
  name             IN VARCHAR2,
  value            IN BOOLEAN|VARCHAR2|NUMBER
);

-- DBMS_METADATA.SESSION_TRANSFORM = 0 (built-in constant for session scope)

Transform Parameter Reference

Parameter	Type	Default	Description
SQLTERMINATOR	BOOLEAN	FALSE	Append ; and / after each statement
PRETTY	BOOLEAN	TRUE	Indent and format the DDL
SEGMENT_ATTRIBUTES	BOOLEAN	TRUE	Include storage, tablespace, and physical attributes
STORAGE	BOOLEAN	TRUE	Include STORAGE (...) clauses
TABLESPACE	BOOLEAN	TRUE	Include TABLESPACE clause
CONSTRAINTS	BOOLEAN	TRUE	Include inline constraint definitions
REF_CONSTRAINTS	BOOLEAN	TRUE	Include referential constraints
CONSTRAINTS_AS_ALTER	BOOLEAN	FALSE	Emit constraints as separate ALTER TABLE statements
EMIT_SCHEMA	BOOLEAN	TRUE	Prefix object names with schema name
OID	BOOLEAN	TRUE	Include object OID clauses

Common Transform Combinations

-- Portable DDL: strip all storage, tablespace, and physical attributes
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM dual;

-- Reset to defaults
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');
END;
/

-- Constraints as separate ALTER TABLE statements (useful for ordered deployment)
BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', FALSE);
END;
/

OPEN / FETCH / CLOSE: Bulk Extraction

For extracting many objects at once, the handle-based API (OPEN, SET_FILTER, SET_COUNT, FETCH_DDL, CLOSE) is more efficient than looping over GET_DDL.

-- Syntax overview
h := DBMS_METADATA.OPEN(object_type IN VARCHAR2) RETURN NUMBER;

DBMS_METADATA.SET_FILTER(
  handle     IN NUMBER,
  name       IN VARCHAR2,
  value      IN VARCHAR2|BOOLEAN|NUMBER
);

DBMS_METADATA.SET_COUNT(handle IN NUMBER, value IN NUMBER);

obj := DBMS_METADATA.FETCH_DDL(handle IN NUMBER) RETURN SYS.KU$_DDLS;
-- SYS.KU$_DDLS is a nested table of SYS.KU$_DDL (which contains CLOB DDL text)

DBMS_METADATA.CLOSE(handle IN NUMBER);

Schema Export with Bulk Fetch

-- Extract all table DDLs for a schema using the handle API
DECLARE
  l_handle   NUMBER;
  l_th       NUMBER;
  l_ddls     SYS.KU$_DDLS;
  l_ddl_text CLOB;
  l_count    NUMBER := 0;
BEGIN
  -- Set session transform options
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

  -- Open a cursor for TABLE objects
  l_handle := DBMS_METADATA.OPEN('TABLE');

  -- Filter to a specific schema
  DBMS_METADATA.SET_FILTER(l_handle, 'SCHEMA', 'HR');

  -- Optional: exclude AQ tables, IOT overflow segments
  DBMS_METADATA.SET_FILTER(l_handle, 'EXCLUDE_TABLE_DATA', TRUE);

  -- Add DDL transform
  l_th := DBMS_METADATA.ADD_TRANSFORM(l_handle, 'DDL');

  LOOP
    l_ddls := DBMS_METADATA.FETCH_DDL(l_handle);
    EXIT WHEN l_ddls IS NULL;

    FOR i IN 1..l_ddls.COUNT LOOP
      l_ddl_text := l_ddls(i).ddltext;
      l_count := l_count + 1;

      -- Process DDL (here just print first 200 chars)
      DBMS_OUTPUT.PUT_LINE('-- Object ' || l_count);
      DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(l_ddl_text, 200, 1));
    END LOOP;
  END LOOP;

  DBMS_METADATA.CLOSE(l_handle);
  DBMS_OUTPUT.PUT_LINE('Total DDL statements: ' || l_count);
END;
/

SET_FILTER: Selective Extraction

-- Common filter names for OPEN/SET_FILTER

-- Filter by schema
DBMS_METADATA.SET_FILTER(l_handle, 'SCHEMA', 'HR');

-- Filter by object name (exact match)
DBMS_METADATA.SET_FILTER(l_handle, 'NAME', 'EMPLOYEES');

-- Filter by name pattern (SQL LIKE)
DBMS_METADATA.SET_FILTER(l_handle, 'NAME_EXPR', 'LIKE ''EMP%''');

-- Exclude system-generated names (e.g., SYS_C0012345 constraints)
DBMS_METADATA.SET_FILTER(l_handle, 'SYSTEM_GENERATED', FALSE);

-- Include only valid objects
DBMS_METADATA.SET_FILTER(l_handle, 'STATUS', 'VALID');

Full Schema DDL Export Script

-- Complete schema export: tables, views, sequences, procedures, indexes, grants
CREATE OR REPLACE PROCEDURE export_schema_ddl (
  p_schema    IN VARCHAR2,
  p_directory IN VARCHAR2,
  p_filename  IN VARCHAR2
) AS
  l_file    UTL_FILE.FILE_TYPE;
  l_ddl     CLOB;
  l_obj_count NUMBER := 0;

  TYPE obj_type_list IS TABLE OF VARCHAR2(30);
  l_types obj_type_list := obj_type_list(
    'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION',
    'PACKAGE', 'PACKAGE_BODY', 'TRIGGER', 'TYPE', 'SYNONYM'
  );

  PROCEDURE write_clob(p_clob IN CLOB) IS
    l_offset  INTEGER := 1;
    l_amount  BINARY_INTEGER := 32767;
    l_buffer  VARCHAR2(32767);
    l_len     INTEGER;
  BEGIN
    l_len := DBMS_LOB.GETLENGTH(p_clob);
    WHILE l_offset <= l_len LOOP
      l_amount := 32767;
      BEGIN
        DBMS_LOB.READ(p_clob, l_amount, l_offset, l_buffer);
        UTL_FILE.PUT(l_file, l_buffer);
      EXCEPTION WHEN NO_DATA_FOUND THEN EXIT;
      END;
      l_offset := l_offset + l_amount;
    END LOOP;
    UTL_FILE.NEW_LINE(l_file);
  END write_clob;

BEGIN
  -- Configure transform for portability
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',     TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',           FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE',        FALSE);

  l_file := UTL_FILE.FOPEN(p_directory, p_filename, 'w', 32767);
  UTL_FILE.PUT_LINE(l_file, '-- Schema export: ' || p_schema);
  UTL_FILE.PUT_LINE(l_file, '-- Generated: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  UTL_FILE.PUT_LINE(l_file, RPAD('-', 80, '-'));
  UTL_FILE.NEW_LINE(l_file);

  FOR i IN 1..l_types.COUNT LOOP
    UTL_FILE.PUT_LINE(l_file, '-- ===== ' || l_types(i) || ' =====');

    DECLARE
      l_handle NUMBER;
      l_th     NUMBER;
      l_ddls   SYS.KU$_DDLS;
    BEGIN
      l_handle := DBMS_METADATA.OPEN(l_types(i));
      DBMS_METADATA.SET_FILTER(l_handle, 'SCHEMA', UPPER(p_schema));
      l_th := DBMS_METADATA.ADD_TRANSFORM(l_handle, 'DDL');

      LOOP
        l_ddls := DBMS_METADATA.FETCH_DDL(l_handle);
        EXIT WHEN l_ddls IS NULL;
        FOR j IN 1..l_ddls.COUNT LOOP
          write_clob(l_ddls(j).ddltext);
          l_obj_count := l_obj_count + 1;
        END LOOP;
      END LOOP;

      DBMS_METADATA.CLOSE(l_handle);
    EXCEPTION
      WHEN OTHERS THEN
        UTL_FILE.PUT_LINE(l_file, '-- WARNING: ' || l_types(i) || ' fetch failed: ' || SQLERRM);
        IF l_handle IS NOT NULL THEN
          BEGIN DBMS_METADATA.CLOSE(l_handle); EXCEPTION WHEN OTHERS THEN NULL; END;
        END IF;
    END;
  END LOOP;

  UTL_FILE.PUT_LINE(l_file, '-- Total objects exported: ' || l_obj_count);
  UTL_FILE.FCLOSE(l_file);
  DBMS_OUTPUT.PUT_LINE('Exported ' || l_obj_count || ' objects to ' || p_filename);

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
    RAISE;
END export_schema_ddl;
/

-- Run the export
EXEC export_schema_ddl('HR', 'EXPORT_DIR', 'hr_schema.sql');

Best Practices

• Reset transform params between unrelated calls. Session-level parameters persist for the session. Call SET_TRANSFORM_PARAM(SESSION_TRANSFORM, 'DEFAULT') to reset.
• Wrap GET_DEPENDENT_DDL in exception handlers. If no dependents exist, it raises ORA-31608 rather than returning NULL.
• Use the handle API for bulk extraction. It is significantly faster than looping GET_DDL across thousands of objects.
• Exclude SEGMENT_ATTRIBUTES and STORAGE for cross-environment migrations. Target environments rarely have identical storage configurations.
• Include SQLTERMINATOR when writing to files. Without it, PL/SQL blocks lack the trailing / and SQL statements lack the ;, making the file unusable as a SQL*Plus script.

Required Privileges

Action	Privilege
Extract your own schema objects	No extra privilege needed
Extract other users’ objects	SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY
Extract user/role DDL	DBA role or SELECT ON SYS.USER$
Extract tablespace DDL	DBA role

Related Topics

• DBMS_REDEFINITION - Reorganize tables online using DDL extracted via DBMS_METADATA
• UTL_FILE - Write extracted DDL to OS files for deployment
• Oracle Data Pump - Full schema export/import using Oracle’s native export tool