prompt Dependencies for Owner?:

col cowner noprint new_value uowner
set term off feed off
select upper('&1') cowner from dual;
set term on feed on

prompt Dependencies for Object?:

col cobject noprint new_value uobject
set term off feed off
select upper('&2') cobject from dual;
set term on feed on

--set line 142 pages 60

column display_parent format a58
column display_child format a58
column referenced_owner noprint
column referenced_object noprint
column referenced_type noprint
column owner noprint
column object noprint
column type noprint
column last_ddl_time format a22 head 'CHILD DDL TIME'

undef 1 2

with dependencies as (
        -- top down through the heirarchy
        select /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' as parent,
                type || ' "' || owner || '"."' || name || '"' as child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, type
        from dba_dependencies
        start with
                referenced_owner = '&&uowner'
                and referenced_name = '&&uobject'
        connect by
                referenced_owner = prior owner
                and referenced_name = prior name
                and referenced_type = prior type
        union
        -- bottom up through the heirarchy
        select /*+ no_merge */
                referenced_type || ' "' || referenced_owner || '"."' ||
                referenced_name || '"' as parent,
                type || ' "' || owner || '"."' || name || '"' as child,
                level hlevel,
                referenced_owner, referenced_name, referenced_type,
                owner, name, type
        from dba_dependencies
        start with
                owner = '&&uowner'
                and name = '&&uobject'
        connect by
                owner = prior referenced_owner
                and name = prior referenced_name
                and type = prior referenced_type
        order by 1, 2
)
select lpad(' ',2*d.hlevel,' ') || d.parent display_parent, d.child 
display_child, o.last_ddl_time 
from dependencies d, dba_objects o
where o.owner = d.owner
and o.object_type = d.type
and d.name = o.object_name
order by parent, child
/