web analytics

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

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

1 comment to Oracle: find dependent objects

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.