« List the tablespaces in backup mode | Home | Oracle: transpose data table (rows into columns) »

Oracle: find dependent objects

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

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#;
 

Topics: Oracle, validation | Submitter: checkthis

Comments

You must be logged in to post a comment.

Keep on coding