Object Dependencies Cross-Reference (dxref.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 by:

• Showing which objects depend on other objects
• Displaying both the dependent object and referenced object
• Supporting filtering by owner, object type, and name
• Providing a cross-reference view of database relationships
• Helping understand object interconnections

Script

rem  dxref.sql
rem
set linesize 132
rem
ttitle 'Object Dependencies by Owner Object'
rem
col owner format a8 heading 'OWNER'
col type format a12 heading 'TYPE'
col name format a30 heading 'NAME'
col referenced_owner format a8 heading 'XREF|OWNER'
col referenced_type format a12 heading 'XREF TYPE'
col referenced_name format a30 heading 'XREF NAME'
rem
select owner, type, name,
       referenced_owner, referenced_type, referenced_name
  from sys.dba_dependencies
 where owner like upper('&owner')
   and type like upper('&type')
   and name like upper('&name')
 order by owner, name, referenced_name;
rem
set linesize 80

Usage

SQL> @dxref.sql
Enter value for owner: HR
Enter value for type: VIEW
Enter value for name: %

Parameters

• owner: Object owner pattern to filter (use % for all)
• type: Object type pattern to filter (use % for all)
• name: Object name pattern to filter (use % for all)

Required Privileges

• SELECT on SYS.DBA_DEPENDENCIES

Sample Output

Object Dependencies by Owner Object

OWNER    TYPE         NAME                           XREF     XREF TYPE    XREF NAME
                                                    OWNER
-------- ------------ ------------------------------ -------- ------------ ------------------------------
HR       VIEW         EMP_DETAILS_VIEW               HR       TABLE        EMPLOYEES
HR       VIEW         EMP_DETAILS_VIEW               HR       TABLE        DEPARTMENTS
HR       VIEW         EMP_DETAILS_VIEW               HR       TABLE        JOBS

HR       PROCEDURE    UPDATE_SALARY                  HR       TABLE        EMPLOYEES
HR       PROCEDURE    UPDATE_SALARY                  HR       TABLE        SALARY_HISTORY
HR       PROCEDURE    UPDATE_SALARY                  SYS      PACKAGE      DBMS_OUTPUT

HR       PACKAGE BODY HR_UTILS                       HR       PACKAGE      HR_UTILS
HR       PACKAGE BODY HR_UTILS                       HR       TABLE        EMPLOYEES
HR       PACKAGE BODY HR_UTILS                       HR       PROCEDURE    UPDATE_SALARY

SCOTT    TRIGGER      EMP_AUDIT_TRG                  SCOTT    TABLE        EMP
SCOTT    TRIGGER      EMP_AUDIT_TRG                  SCOTT    TABLE        EMP_AUDIT

Key Output Columns

• OWNER: Schema that owns the dependent object
• TYPE: Type of the dependent object (VIEW, PROCEDURE, etc.)
• NAME: Name of the dependent object
• XREF OWNER: Schema that owns the referenced object
• XREF TYPE: Type of the referenced object
• XREF NAME: Name of the referenced object

Understanding Object Dependencies

Common Dependency Types

Tables → Objects

• Views: Built on tables
• Triggers: Attached to tables
• Procedures: Access table data
• Functions: Query tables

Packages → Objects

• Package Bodies: Depend on package specs
• Procedures: Call package functions
• Views: Use package functions

System Dependencies

• SYS objects: Standard Oracle packages
• Built-in functions: SQL and PL/SQL functions
• Data types: User-defined types

Common Use Cases

1. Impact Analysis

   • Assess changes before implementation
   • Identify affected objects
   • Plan deployment order

2. Documentation

   • Create system architecture diagrams
   • Document data flows
   • Map application components

3. Maintenance Planning

   • Plan object modifications
   • Identify compilation order
   • Schedule maintenance windows

4. Troubleshooting

   • Find broken dependencies
   • Resolve invalid objects
   • Debug compilation errors

Advanced Analysis

Find objects with no dependencies:

SELECT owner, object_name, object_type
FROM dba_objects
WHERE (owner, object_name) NOT IN (
    SELECT owner, name FROM dba_dependencies
)
AND owner NOT IN ('SYS','SYSTEM')
AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION')
ORDER BY owner, object_name;

Show dependency hierarchy:

-- Find all objects that depend on EMPLOYEES table
SELECT LEVEL, LPAD(' ', (LEVEL-1)*2) || owner||'.'||name dependency_chain
FROM dba_dependencies
START WITH referenced_owner = 'HR'
       AND referenced_name = 'EMPLOYEES'
CONNECT BY PRIOR owner = referenced_owner
       AND PRIOR name = referenced_name
ORDER SIBLINGS BY owner, name;

Count dependencies by object:

SELECT referenced_owner, referenced_name, referenced_type,
       COUNT(*) dependent_count
FROM dba_dependencies
WHERE referenced_owner NOT IN ('SYS','SYSTEM')
GROUP BY referenced_owner, referenced_name, referenced_type
HAVING COUNT(*) > 5
ORDER BY dependent_count DESC;

Dependency Management

Compilation Order

When recompiling objects, compile in dependency order:

1. Tables (base objects)
2. Types and Type Bodies
3. Package Specs
4. Package Bodies
5. Procedures/Functions
6. Views
7. Triggers

Safe Modification Process

-- 1. Identify dependencies
SELECT owner, name, type
FROM dba_dependencies
WHERE referenced_owner = 'HR'
AND referenced_name = 'EMPLOYEES';

-- 2. Plan modification impact
-- 3. Modify base object
-- 4. Recompile dependent objects

Breaking Dependencies

Drop Cascade Options

-- Drop table and dependent objects
DROP TABLE hr.employees CASCADE CONSTRAINTS;

-- Drop with dependency checking
SELECT 'Cannot drop - dependencies exist'
FROM dba_dependencies
WHERE referenced_owner = 'HR'
AND referenced_name = 'EMPLOYEES'
AND ROWNUM = 1;

Handling Invalid Objects

-- Find invalid objects
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

-- Recompile invalid objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');

Performance Considerations

• DBA_DEPENDENCIES can be large in complex systems
• Use filters to limit result sets
• Index on owner/name columns helps performance
• Consider using tools for complex dependency analysis

Related Scripts

• Object Dependency Analyzer
• Database Objects
• View Definitions
• Compilation Errors