DBA Dependencies (used by)

Description
Categories: Enginatics
Repository: Github
Hierarchical report showing all database objects using a specified object
select
'0' level_,
lower(do.owner||'.'||do.object_name) object,
do.owner,
do.object_name,
do.object_type,
lower(do.owner||'.'||do.object_name) path
from
dba_objects do
where
do.owner=:owner and
do.object_name=:object_name and
do.object_type=:object_type and
1=1
union all
select
lpad(' ',2*(level))||level level_,
lower(lpad(' ',2*(level))||dd.owner||'.'||dd.name) object,
dd.owner owner,
dd.name object_name,
dd.type object_type,
substr(lower(xxen_util.reverse(sys_connect_by_path(dd.owner||'.'||dd.name,' > '),' > ')||' > '||:owner||'.'||:object_name),4) path
from
dba_dependencies dd
where
dd.owner not in ('SYS','SYSTEM','PUBLIC') and
dd.referenced_owner not in ('SYS','SYSTEM','PUBLIC') and
dd.referenced_type<>'NON-EXISTENT'
connect by nocycle
prior dd.owner=dd.referenced_owner and
prior dd.name=dd.referenced_name and
prior dd.type=dd.referenced_type
start with
dd.referenced_owner=:owner and
dd.referenced_name=:object_name and
dd.referenced_type=:object_type
Parameter Name SQL text Validation
Owner
 
LOV
Object Name
 
LOV
Object Type
 
LOV