Deps

Dependency report for specified object (table, package etc) using DBA_DEPENDENCIES view.

-- deps.sql
-- Find dependencies for specified object
-- William Robertson 2004, www.williamrobertson.net
--:vim:set ts=8

set term off
store set sqlplus_settings.sql replace
set autoprint off term off

-- Use DBA_ views if possible:
col dict_level new_value dict_level
select 'ALL' as dict_level from dual;
select 'DBA' as dict_level from dba_users where rownum = 1;
-- Now &DICT_LEVEL contains either 'ALL' or 'DBA'
col dict_level clear

col name format a30

var arg    varchar2(100)
exec :arg := upper('&1')
var owner  varchar2(30)
var object varchar2(30)

-- Poor man's name tokenizer:
begin
	if :arg like '%.%'
	then
		:owner := substr(:arg,1,instr(:arg,'.') -1);
		:object := substr(:arg,instr(:arg,'.') +1);
	else
		:owner := sys_context('userenv','current_schema');
		:object := :arg;
	end if;
end;
/

@sqlplus_settings.sql
set term on

break on dependency_type skip1 nodup
col dependency_type format a14 hea "dependency|type"

select 'Referenced by' as dependency_type
     , owner
     , name
     , type
from   &dict_level._dependencies
where  referenced_owner = :owner
and    referenced_name like :object escape '\'
and    not (name = :object and owner = :owner)
union
select 'references'
     , referenced_owner
     , referenced_name
     , referenced_type
from   &dict_level._dependencies
where  owner = :owner
and    name like :object escape '\'
and    not (referenced_name = :object and referenced_owner = :owner)
order by 1,2,3,4
/

Source code: //www.williamrobertson.net/code/deps.sql