select
lpad(' ',2*(level))||level level_,
lower(lpad(' ',2*(level))||dd.referenced_owner||'.'||dd.referenced_name) object,
dd.referenced_owner owner,
dd.referenced_name object_name,
dd.referenced_type object_type,
lower(decode(level,1,dd.owner||'.'||dd.name)||sys_connect_by_path(dd.referenced_owner||'.'||dd.referenced_name,' > ')) 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.referenced_owner=dd.owner and
prior dd.referenced_name=dd.name and
prior dd.referenced_type=dd.type
start with
(dd.owner,dd.name,dd.type) in (
select
rv.schema_name,
rv.db_view_name,
(select do.object_type from dba_objects do where rv.view_name=do.object_name and rv.schema_name=do.owner and do.object_type in ('VIEW','TABLE')) object_type
from
(
with x as (select distinct rrs.report_id from rx_report_stats rrs where rrs.start_time>sysdate-:history_days)
select
rvc.view_id
from
rx_report_columns rrc,
rx_view_columns rvc
where
rrc.report_id in (select x.report_id from x) and
rrc.view_column_id=rvc.view_column_id and
rrc.enabled_flag='Y' union
select rvj.source_view_id view_id from rx_view_joins rvj where rvj.join_id in (select rrj.join_id from rx_report_joins rrj where rrj.report_id in (select x.report_id from x) and rrj.enabled_flag='Y') union
select rvj.dest_view_id view_id from rx_view_joins rvj where rvj.join_id in (select rrj.join_id from rx_report_joins rrj where rrj.report_id in (select x.report_id from x) and rrj.enabled_flag='Y')
) y,
rx_views rv
where
y.view_id=rv.view_id
) |
