Tag Archives: constraint

Oracle: find dependent objects

Posted on by
1

This query helps to find the objects, that depend from the specified one

select CONSTRAINT_NAME, OWNER,TABLE_NAME from DBA_CONSTRAINTS
where R_CONSTRAINT_NAME in
( select CONSTRAINT_NAME
from dba_constraints
where
OWNER=upper('&user_dependent_from') and
TABLE_NAME=upper('&object_dependent_from') )
and STATUS='ENABLED';

Example:

create table city ( city_id int ,
name char(10) ,
primary key ( city_id ) ) ;

create table country (
cou_id int,
name char(19),
city_id int
constraint ZZZ references city ( city_id ) on delete cascade,
primary key(cou_id) ) ;

insert into CITY values ( 1, 'Paris' ) ;
insert into CITY values ( 2, 'Berlin') ;

insert into COUNTRY values ( 101, 'France', 1 ) ;
insert into COUNTRY values ( 102, 'Germany', 2 );

Output:

Enter value for user_dependent_from: FRODO
Enter value for object_dependent_from: CITY

CONSTRAINT_NAME OWNER TABLE_NAME
ZZZ FRODO COUNTRY

[amazon-product]1590599683[/amazon-product]

There is also standard script from Oracle, which displays the dependencies: ${ORACLE_HOME}/rdbms/admin/utldtree.sql


execute deptree_fill('table', 'scott', 'emp');
select * from deptree order by seq#;