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
Answer: Oracle has several ways.
One is the dba_dependencies view and we also have the utldtree
Using the dba_dependencies View to display
See here for my full notes and script on
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"
column c2 heading "object|name"
column c3 heading "referencing|object" format a40
lpad (' ', 2 * (level - 1)) || to_char (level, '999') as c1,
owner || '.' || name || ' (' || type || ')' as c2,
referenced_owner || '.' || referenced_name || ' (' ||
referenced_type || ')' as c3
owner = 'SYS'
name = 'my_tablename'
connect by prior
referenced_owner = owner
referenced_name = name
referenced_type = type
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
When you execute utldtree.sql, a procedure
named deptree_fill is created, and youi specify the object name and type:
You are now able to execute the deptree_fill
procedure, followed by a query to the resulting ideptree table:
SQL> select * from
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.