DBA_DEPENDENCIES
Dependencies to and from objects
| Name | Datatype | Length | Mandatory | Comments |
|---|---|---|---|---|
| OWNER | VARCHAR2 | (30) | Yes | Owner of the object |
| NAME | VARCHAR2 | (30) | Yes | Name of the object |
| TYPE | VARCHAR2 | (17) | Type of the object | |
| REFERENCED_OWNER | VARCHAR2 | (30) | Owner of referenced object (remote owner if remote object) | |
| REFERENCED_NAME | VARCHAR2 | (64) | Name of referenced object | |
| REFERENCED_TYPE | VARCHAR2 | (17) | Type of referenced object | |
| REFERENCED_LINK_NAME | VARCHAR2 | (128) | Name of dblink if this is a remote object | |
| DEPENDENCY_TYPE | VARCHAR2 | (4) |
Usage:
selects from the dependency$ table instead of dba_dependencies. The dba_dependencies view is slow.
declare
procedure show_dependencies( p_object_name IN dba_objects.object_name%type
, p_object_type in dba_objects.object_type%type
, p_owner in dba_objects.owner%type) is
cursor cur_depend(n_object_id IN dba_objects.object_id%type)
is select d_obj# object_id
, object_name
, object_type
, owner
from sys.dependency$
, dba_objects
where p_obj# = n_object_id
and d_obj# = object_id
order by owner, object_name, object_type;
--
cursor cur_depend2(n_object_id IN dba_objects.object_id%type)
is select p_obj#
, object_name
, object_type
, owner
from sys.dependency$
, dba_objects
where d_obj# = n_object_id
and p_obj# = object_id
order by owner, object_name, object_type;
v_object_id dba_objects.object_id%type;
begin
select object_id
into v_object_id
from dba_objects
where object_name = p_object_name
and object_type = p_object_Type
and owner = p_owner;
dbms_output.put_line('The ' ||p_object_name ||' '
||lower(p_object_type)
||' is referenced by the following objects:');
for d_rec in cur_depend(v_object_id) loop
dbms_output.put_line(d_rec.owner||'.'||d_rec.object_name||' ('||d_rec.object_type||')');
end loop;
dbms_output.put_line('--------------------');
dbms_output.put_line('The ' ||p_object_name ||' '||lower(p_object_type)||' references:');
for d_rec in cur_depend2(v_object_id) loop
dbms_output.put_line(d_rec.owner||'.'||d_rec.object_name||' ('||d_rec.object_type||')');
end loop;
dbms_output.put_line('--------------------');
end show_dependencies;
begin
show_dependencies('MY-TABLE', 'TABLE', 'ARU');
show_dependencies('MY-VIEW';, 'VIEW', 'ARU');
show_dependencies('MY-PACKAGE', 'PACKAGE', 'ARU');
end;
/