Oracle - Tracking constraint dependencies Oracle Database Tips by Donald Burleson

Question:  I know that the Oracle data pump utility (expdp and impdp) will extract and re-add tables in their proper order to avoid constraint violations.  What are the other Oracle data dictionary views that are used to track object dependencies and how do I write a script to display tables in their reverse-order of constrain dependence?

Answer:  Oracle has several ways.  One is the dba_dependencies view and we also have the utldtree utility.

Using the dba_dependencies View to display dependencies

See here for my full notes and script on dba_dependencies.  Here is a global script to display all tables which have dependencies upon other tables.  Just change the "my_tablename" value:

column c1 heading "object|level"         format a16
column c2 heading "object|name"          format a40
column c3 heading "referencing|object"   format a40

select
   lpad (' ', 2 * (level - 1)) || to_char (level, '999') as c1,
   owner || '.' || name || ' (' || type || ')' as c2,
   referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')' as c3
from
   dba_dependencies
start with
   owner = 'SYS'
and
   name = 'my_tablename'
connect by prior
   referenced_owner = owner
and prior
   referenced_name = name
and prior
   referenced_type = type
and
   type = 'TABLE';

Using the utldtree utility

Oracle provides utldtree.sql in your $ORACLE_HOME/rdbms/admin directory for displaying any object (tables, stored procedures) and all of their dependencies.

When you execute utldtree.sql, a procedure named deptree_fill is created, and youi specify the object name and type:

deptree_fill('object_type', 'object_owner', 'object_name');

You are now able to execute the deptree_fill procedure, followed by a query to the resulting ideptree table:

SQL> exec deptree_fill('TABLE','SCOTT','EMP');

SQL> select * from ideptree;

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.