>-----Original Message-----
>From: Yttri, Lisa [mailto:[EMAIL PROTECTED]]
>
>Does anyone have (or know where I can find) a script to identify
> dependencies on a table? When we make table modifications,
> we would like to be able to run a script that recursively
> identifies dependent objects. We pulled one from Metalink,
> but it doesn't seem to work.
This is probably not the best or most efficient way, but it's a "quick and dirty" approach. It should give you the correct results as long as you don't have objects that depend on themselves.
create table my_dependencies as select * from dba_dependencies ;
set linesize 120
column parent format a58
column child format a58
define owner = "XXX"
define object = "YYY"
select
referenced_type || ' "' || referenced_owner || '"."' ||
referenced_name || '"' as parent,
type || ' "' || owner || '"."' || name || '"' as child
from
my_dependencies
start with
referenced_owner = '&owner'
and referenced_name = '&object'
connect by
referenced_owner = prior owner
and referenced_name = prior name
and referenced_type = prior type
union
select
referenced_type || ' "' || referenced_owner || '"."' ||
referenced_name || '"' as parent,
type || ' "' || owner || '"."' || name || '"' as child
from
my_dependencies
start with
owner = '&owner'
and name = '&object'
connect by
owner = prior referenced_owner
and name = prior referenced_name
and type = prior referenced_type
order by
1, 2 ;
drop table my_dependencies ;
