Get Recursive Object Dependencies in Oracle 12.1

Posted on December 7, 2020

It can be helpful to get a list of recursive dependencies for a schema object (e.g., a view) in an Oracle database. The following hierarchical query returns all of the distinct recursive dependencies for a given starting object.

select distinct
    referenced_owner,
    referenced_name
from all_dependencies
start with
    owner = 'starting object owner'
    and name = 'starting object name'
connect by
    prior referenced_name = name
    and prior referenced_owner = owner

Note that the ALL_DEPENDENCIES view only includes objects that are accessible to you, so you will not see the dependencies of objects that are in other schemas unless you have been given access to them.