DBA_DEPENDENCIES - Trace Oracle Object Dependencies

DBA_DEPENDENCIES

Overview

DBA_DEPENDENCIES records the compile-time dependency relationships between database objects. When a PL/SQL procedure references a table, when a view references another view, or when a package body calls a function in another package, Oracle records the relationship here. Each row describes a single directed edge: an object (the dependent) that depends on another object (the referenced). DBAs use this view to predict the impact of a schema change before it happens, to diagnose cascading invalidation after a DDL event, and to sequence object creation and recreation during migrations or deployments.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_DEPENDENCIES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the dependent object
NAME	VARCHAR2(128)	Name of the dependent object
TYPE	VARCHAR2(17)	Type of the dependent object: TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, etc.
REFERENCED_OWNER	VARCHAR2(128)	Schema that owns the referenced object
REFERENCED_NAME	VARCHAR2(128)	Name of the referenced object
REFERENCED_TYPE	VARCHAR2(17)	Type of the referenced object
REFERENCED_LINK_NAME	VARCHAR2(128)	Database link name if the reference crosses a database link; NULL for local references
SCHEMAID	NUMBER	Internal schema identifier for the dependent object
DEPENDENCY_TYPE	VARCHAR2(4)	HARD (compile-time dependency) or SOFT (runtime or late-binding dependency)

Essential Queries

Basic Usage

List all objects that directly depend on a specific table — the objects that will be invalidated if the table is altered:

SELECT
    owner,
    name,
    type,
    dependency_type
FROM
    dba_dependencies
WHERE
    referenced_owner = 'APP_OWNER'
    AND referenced_name = 'ORDERS'
    AND referenced_type = 'TABLE'
ORDER BY
    type,
    name;

Monitoring Query

Find all INVALID objects in a schema and their direct dependencies to understand what caused the invalidation:

SELECT
    o.owner,
    o.object_name,
    o.object_type,
    o.status,
    o.last_ddl_time,
    d.referenced_owner,
    d.referenced_name,
    d.referenced_type
FROM
    dba_objects     o
    JOIN dba_dependencies d
        ON  d.owner = o.owner
        AND d.name  = o.object_name
        AND d.type  = o.object_type
WHERE
    o.status  = 'INVALID'
    AND o.oracle_maintained = 'N'
ORDER BY
    o.owner,
    o.object_name,
    d.referenced_name;

Combined with Other Views

Recursively expand the dependency chain for a procedure to show all objects in the full dependency tree, useful before a major refactoring:

SELECT
    LPAD(' ', 4 * (LEVEL - 1)) || name        AS dependent_object,
    type,
    referenced_owner                           AS ref_owner,
    referenced_name,
    referenced_type,
    LEVEL                                      AS depth
FROM
    dba_dependencies
START WITH
    referenced_owner = 'APP_OWNER'
    AND referenced_name = 'GET_ORDER_TOTAL'
    AND referenced_type = 'FUNCTION'
CONNECT BY NOCYCLE PRIOR
    owner           = referenced_owner
    AND name        = referenced_name
    AND type        = referenced_type
ORDER SIBLINGS BY
    name;

Cross-Schema Dependency Audit

Find dependencies where one schema’s objects reference another schema’s objects — important for understanding coupling between application modules:

SELECT
    owner                                       AS dependent_schema,
    name                                        AS dependent_object,
    type                                        AS dependent_type,
    referenced_owner                            AS referenced_schema,
    referenced_name                             AS referenced_object,
    referenced_type,
    dependency_type
FROM
    dba_dependencies
WHERE
    owner != referenced_owner
    AND owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
    AND referenced_owner NOT IN (
        SELECT username FROM dba_users WHERE oracle_maintained = 'Y'
    )
ORDER BY
    dependent_schema,
    dependent_object,
    referenced_schema,
    referenced_object;

Impact Analysis Before Drop or Rename

Find the full set of objects that would be affected by dropping or renaming a specific package:

SELECT
    d.owner,
    d.name,
    d.type,
    o.status,
    o.last_ddl_time
FROM
    dba_dependencies d
    JOIN dba_objects o
        ON  o.owner       = d.owner
        AND o.object_name = d.name
        AND o.object_type = d.type
WHERE
    d.referenced_owner = 'APP_OWNER'
    AND d.referenced_name IN ('PKG_ORDER_PROCESSING', 'PKG_INVOICE')
    AND d.referenced_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY
    o.status DESC,
    d.type,
    d.name;

Common Use Cases

• Pre-DDL impact analysis — Before adding a column to a table, altering a package interface, or dropping a synonym, query DBA_DEPENDENCIES to enumerate every object that will be invalidated and must be recompiled
• Post-patch invalidation recovery — After applying an Oracle patch, query for INVALID objects and use DBA_DEPENDENCIES to determine the correct recompilation order — referenced objects must be valid before dependents can compile
• Migration sequencing — When scripting a schema export and rebuild, walk DBA_DEPENDENCIES to produce a topologically sorted creation script that builds base tables first, then views, then procedures
• Cyclic dependency detection — Use CONNECT BY NOCYCLE to detect circular dependencies between packages, which prevent clean compilation and indicate design problems
• Database link dependency audit — Filter on REFERENCED_LINK_NAME IS NOT NULL to find all local objects that depend on remote objects through database links — these will break if the link is dropped or the remote database is renamed
• Application component coupling analysis — Map cross-schema dependencies to identify tightly coupled modules that should be decoupled as part of a microservices or schema separation initiative

Related Views

• DBA_CONSTRAINTS — Foreign key constraints are a runtime form of dependency; DBA_DEPENDENCIES captures compile-time dependencies
• DBA_SOURCE — The source code of dependent PL/SQL objects; use alongside DBA_DEPENDENCIES to understand why a dependency exists
• DBA_OBJECTS — Status (VALID/INVALID), last DDL time, and object type for every object referenced in DBA_DEPENDENCIES
• DBA_SYNONYMS — Synonyms create soft dependencies; a synonym pointing to a non-existent object does not appear in DBA_DEPENDENCIES until the synonym is resolved at compile time
• UTL_RECOMP — PL/SQL utility for recompiling invalid objects in dependency order; its logic mirrors what DBA_DEPENDENCIES enables manually

Version Notes

• Oracle 7: View present from early releases for PL/SQL dependencies on tables and other PL/SQL units
• Oracle 8i: DEPENDENCY_TYPE column added to distinguish HARD and SOFT (dynamic) dependencies
• Oracle 9i: Improved fine-grained dependency tracking introduced (procedure-level and element-level dependency awareness), reducing unnecessary invalidations
• Oracle 10g: Edition-based redefinition groundwork; dependencies on editioned objects tracked separately in some configurations
• Oracle 11g: Fine-grained dependency tracking further enhanced; altering a table column that is not referenced by a procedure no longer invalidates the procedure
• Oracle 12c: CON_ID column available when querying from the CDB root; dependencies across PDB boundaries visible from the root container
• Oracle 23ai: JSON Relational Duality Views introduce new dependency types as views depend on their underlying tables in a structured way