Object Dependency Analyzer (ddepend.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes object dependencies in the database, showing all objects that depend on a specific referenced object. It helps identify impact analysis before making changes to database objects by revealing which procedures, packages, views, and other objects would be affected by modifications to the referenced object.

Script

ttitle 'DEPENDECIES'
rem
set linesize 132
rem
col OWNER format a6 heading 'OWNER'
col NAME format a25 heading 'NAME'
col TYPE format a8 heading 'TYPE'
col REFERENCED_OWNER format a6 heading 'REF.|OWNER'
col REFERENCED_NAME format a25 heading 'REF.|NAME'
col REFERENCED_TYPE format a8 heading 'REF.|TYPE'
col REFERENCED_LINK_NAME format a6 heading 'REF.|LINK'
col DEPENDENCY_TYPE format a8 heading 'DEP.|TYPE'
rem
break on REFERENCED_OWNER -
    on REFERENCED_NAME -
    on REFERENCED_TYPE -
    on REFERENCED_LINK_NAME -
    on DEPENDENCY_TYPE
select
    REFERENCED_OWNER,
    REFERENCED_NAME,
    REFERENCED_TYPE,
    REFERENCED_LINK_NAME,
    DEPENDENCY_TYPE,
    OWNER,
    NAME,
    TYPE
from dba_dependencies
where referenced_owner like upper('&ref_owner')
and referenced_name like upper('&ref_object')
order by
    REFERENCED_OWNER,
    REFERENCED_NAME,
    REFERENCED_TYPE,
    REFERENCED_LINK_NAME,
    DEPENDENCY_TYPE
/

Usage

-- Run the script in SQL*Plus or SQLcl
@ddepend.sql

-- When prompted, enter:
-- ref_owner: Owner of the referenced object (use % for all)
-- ref_object: Name of the referenced object (use % for pattern matching)

-- Examples:
Enter value for ref_owner: HR
Enter value for ref_object: EMPLOYEES

-- Find all dependencies on tables starting with 'EMP'
Enter value for ref_owner: HR
Enter value for ref_object: EMP%

Parameters

• &ref_owner - Schema owner of the referenced object
• &ref_object - Name of the referenced object (supports wildcards)

Required Privileges

• SELECT on DBA_DEPENDENCIES
• Typically requires DBA role or SELECT ANY DICTIONARY

Sample Output

DEPENDECIES

REF.   REF.                      REF.     REF.  DEP.
OWNER  NAME                      TYPE     LINK  TYPE     OWNER  NAME                      TYPE
------ ------------------------- -------- ----- -------- ------ ------------------------- --------
HR     EMPLOYEES                 TABLE          HARD     HR     EMP_DETAILS_VIEW          VIEW
                                                         HR     ADD_JOB_HISTORY           PROCEDURE
                                                         HR     SECURE_EMPLOYEES          TRIGGER
                                                         PAYROL CALC_SALARY               FUNCTION
                                                REF      HR     EMP_EMAIL_LIST            VIEW
                                                         HR     UPDATE_JOB_HISTORY        PROCEDURE

       DEPARTMENTS               TABLE          HARD     HR     EMP_DETAILS_VIEW          VIEW
                                                         HR     DEPT_MANAGER_VIEW         VIEW
                                                         HR     DEPT_SUMMARY              PACKAGE

Key Output Columns

• REF. OWNER - Schema owning the referenced object
• REF. NAME - Name of the referenced object
• REF. TYPE - Type of referenced object (TABLE, VIEW, etc.)
• REF. LINK - Database link name if remote dependency
• DEP. TYPE - Type of dependency (HARD or REF)
• OWNER - Schema owning the dependent object
• NAME - Name of the dependent object
• TYPE - Type of dependent object

Understanding Dependencies

Dependency Types

• HARD - Direct dependency, object will become invalid if referenced object changes
• REF - Reference dependency, may not cause invalidation

Common Object Types

• TABLE - Base tables
• VIEW - Views referencing the object
• PROCEDURE/FUNCTION - Stored programs using the object
• PACKAGE - Package specifications or bodies
• TRIGGER - Triggers on or referencing the object

Common Use Cases

Impact Analysis Before Changes

@ddepend.sql
-- Before dropping or modifying a table
Enter value for ref_owner: HR
Enter value for ref_object: EMPLOYEES
-- Review all dependent objects

Find Package Dependencies

@ddepend.sql
-- Find what uses a specific package
Enter value for ref_owner: DBMS_OUTPUT
Enter value for ref_object: %

Cross-Schema Dependencies

@ddepend.sql
-- Find dependencies across schemas
Enter value for ref_owner: %
Enter value for ref_object: CUSTOMER_DATA

Troubleshooting Dependencies

Invalid Objects After Changes

-- Find invalidated objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
AND (owner, object_name) IN (
    SELECT owner, name
    FROM dba_dependencies
    WHERE referenced_name = '&object_name'
);

Circular Dependencies

-- Detect circular dependencies
WITH dep_tree AS (
    SELECT owner, name, type,
           referenced_owner, referenced_name, referenced_type,
           LEVEL as dep_level
    FROM dba_dependencies
    START WITH owner = '&owner' AND name = '&name'
    CONNECT BY PRIOR referenced_owner = owner
           AND PRIOR referenced_name = name
)
SELECT * FROM dep_tree
WHERE (referenced_owner, referenced_name) =
      ((SELECT owner, name FROM dep_tree WHERE dep_level = 1));

Remote Dependencies

-- Find dependencies through database links
SELECT * FROM dba_dependencies
WHERE referenced_link_name IS NOT NULL
AND referenced_owner = '&owner';

Best Practices

Before Making Changes

1. Always run dependency analysis first
2. Document all dependent objects
3. Plan for recompilation needs
4. Consider cascade effects

Managing Dependencies

• Minimize cross-schema dependencies
• Use packages to encapsulate logic
• Document dependency chains
• Test impact in development first

Performance Considerations

• Large databases may have thousands of dependencies
• Use specific object names rather than wildcards when possible
• Consider creating custom dependency views for frequent analysis
• Index DBA_DEPENDENCIES can improve query performance

Related Scripts

• Invalid Objects (../administration/dinvalid.md) - Find invalid objects
• Database Objects (dobject.md) - Object overview
• Compile Invalid Objects (../generators/compile_invalid.md) - Recompile after changes

Advanced Analysis

Dependency Tree

-- Show full dependency tree
SELECT LPAD(' ', 2*(LEVEL-1)) || name as dependency_tree,
       owner, type, referenced_name, referenced_type
FROM dba_dependencies
START WITH referenced_owner = '&owner'
       AND referenced_name = '&object'
CONNECT BY PRIOR owner = referenced_owner
       AND PRIOR name = referenced_name;

Dependency Depth

-- Find objects with deepest dependency chains
WITH dep_depth AS (
    SELECT name, owner, MAX(LEVEL) as max_depth
    FROM dba_dependencies
    CONNECT BY PRIOR owner = referenced_owner
           AND PRIOR name = referenced_name
    GROUP BY name, owner
)
SELECT * FROM dep_depth
WHERE max_depth > 3
ORDER BY max_depth DESC;