dba_dependencies对象依赖关系

 

--1.自上往下找依赖对象

CREATE OR REPLACE Function Get_Ref_Name(Pi_Name Varchar2)
 Return Varchar2 Is
  l_Name Varchar2(32);
  l_Name_String Varchar2(4000);
Begin
   For Cur_Name In (Select aa.owner,Referenced_Name,referenced_owner
                     From Dba_Dependencies aa
                    Where Name = Pi_Name
                      And Referenced_Owner <> 'SYS'
                      And aa.referenced_name <> Pi_Name
                      ) Loop
    Dbms_Output.Put_Line(cur_name.owner||'.'||Pi_Name || '-----' ||Cur_Name.referenced_owner||'.'|| Cur_Name.Referenced_Name);
    l_Name := Get_Ref_Name(Cur_Name.Referenced_Name);
  End Loop;
  Return Nvl(l_Name, 'please check output!');
End;

2.自下往上找依赖对象

create or replace function get_rev_ref_name(pi_name varchar2)
  return varchar2 is
  l_name varchar2(32);
  l_name_string varchar2(4000);
begin
  for cur_name in (select name
                     from dba_dependencies
                    where referenced_name = pi_name
                      and referenced_owner <> 'SYS') loop
    dbms_output.put_line(pi_name||'-----'||cur_name.name);

    l_name := get_rev_ref_name(cur_name.name);
  end loop;
  return nvl(l_name,'please check output!');
end;

select get_rev_ref_name('your object name')from dual