declare
type linkobj is record (owner varchar2(30), linkname varchar2(128));
type linkstab is table of linkobj;
type rstab is table of dba_dependencies%rowtype;
rs rstab := rstab();
links linkstab;
v_clob clob;
begin
select owner,db_link bulk collect into links from dba_db_links;
select * bulk collect into rs from
(select * from dba_dependencies where referenced_link_name is not null
union
select owner, synonym_name, 'SYNONYM', table_owner, table_name, 'TABLE', db_link, null
from dba_synonyms where db_link is not null
union
select owner, mview_name, 'MVIEW', null, null, null, ltrim(master_link,'@'), null
from dba_mviews where master_link is not null
);
begin
for c in (select owner, view_name, text from dba_views)
loop
v_clob := upper(to_clob(c.text));
for i in links.first..links.last
loop
if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
then
rs.extend;
rs(rs.count).owner := c.owner;
rs(rs.count).name := c.view_name;
rs(rs.count).type := 'VIEW';
rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
end if;
end loop;
end loop;
exception when others then null;
end;
begin
for c in (select owner, trigger_name, trigger_body from dba_triggers)
loop
v_clob := upper(to_clob(c.trigger_body));
for i in links.first..links.last
loop
if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
then
rs.extend;
rs(rs.count).owner := c.owner;
rs(rs.count).name := c.trigger_name;
rs(rs.count).type := 'TRIGGER';
rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
end if;
end loop;
end loop;
exception when others then null;
end;
begin
for c in (select schema_user, job, what from dba_jobs)
loop
v_clob := upper(to_clob(c.what));
for i in links.first..links.last
loop
if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.schema_user = links(i).owner
then
rs.extend;
rs(rs.count).owner := c.schema_user;
rs(rs.count).name := c.job;
rs(rs.count).type := 'JOB';
rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
end if;
end loop;
end loop;
exception when others then null;
end;
begin
for c in (select owner, job_name, job_action from dba_scheduler_jobs)
loop
v_clob := upper(to_clob(c.job_action));
for i in links.first..links.last
loop
if dbms_lob.instr(v_clob, links(i).linkname, 1, 1) > 0 and c.owner = links(i).owner
then
rs.extend;
rs(rs.count).owner := c.owner;
rs(rs.count).name := c.job_name;
rs(rs.count).type := 'SCHJOB';
rs(rs.count).referenced_link_name :=dbms_lob.substr(v_clob,length(links(i).linkname),dbms_lob.instr(v_clob,'@',1,1)+1);
end if;
end loop;
end loop;
exception when others then null;
end;
dbms_output.put_line('owner,oname,otype,rowner,rname,rtype,dblink');
for i in rs.first..rs.last
loop
dbms_output.put_line
(
rs(i).owner||','||
rs(i).name||','||
rs(i).type||','||
rs(i).referenced_owner||','||
rs(i).referenced_name||','||
rs(i).referenced_type||','||
rs(i).referenced_link_name
);
end loop;
end;
/