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';
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 );
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: CITYCONSTRAINT_NAME OWNER TABLE_NAME
ZZZ FRODO COUNTRY
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#;
SELECT * FROM deptree ORDER BY seq#;
Excellent…Thank you.