Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume the number of tables is huge in a schema. What would be the query that gives me the list of tables in that order which I can perform migration.

  • instead of "assume that" tell us some actual numbers. If you have one million 2 row tables that is different than one 2 million row table. Since it's Oracle I'll assume there's magic I don't know about for the reason to migrate some set of tables separately from the others. On SQL Server I would just do them all together... Are you using any other tools or ONLY the default Oracle tools?
    – jcolebrand
    Apr 29, 2011 at 5:04

2 Answers 2


To do that you could try:

   owner, name,
   referenced_owner, referenced_name
start with
    owner = '${YOUR_TABLE_OWNER}'
and name = '${YOUR_TABLE_NAME}'
connect by prior referenced_owner = owner
       and prior referenced_name = name
       and prior referenced_type = type
and type = 'TABLE';

Datapump uses a similar strategy to prevent constraint violations. I hope this helps.


Starting with Frank Kulash's example of a hierarchial query against the DBA_CONSTRAINTS table, something like

WITH    constraint_tree AS
        a.table_name    AS table_name
    ,   b.table_name    AS parent_table_name
    FROM        dba_constraints a
    LEFT OUTER JOIN dba_constraints b
    ON      a.r_constraint_name = b.constraint_name
    AND     a.owner         = b.owner
    WHERE       a.owner = 'SCOTT'
SELECT table_name, lvl
  FROM (
    SELECT a.*,
           rank() over (partition by table_name order by lvl desc) rnk
      FROM (
        SELECT  table_name, level lvl
          FROM  constraint_tree
         START WITH parent_table_name   IS NULL
       CONNECT BY NOCYCLE parent_table_name = PRIOR table_name
      ) a 
    ) b
 WHERE rnk = 1
 ORDER BY lvl, table_name

will give you the tables in the order they should be loaded (assuming there are no cycles in the data). If you want to load in parallel, all tables with the same LVL can be loaded simultaneously.

  • One nit: a table that is relationally neither a parent nor a child will not be captured by that script.
    – Adam Musch
    May 3, 2011 at 16:46
  • @Adam - If there is a constraint on the table (i.e. if there is a primary key constraint), this query should pick it up because of the LEFT OUTER JOIN. If there are no constraints on the table, you're right, it will get left out. But, presumably, that's pretty rare. I suppose I should start at DBA_TABLES rather than DBA_CONSTRAINTS in the CONSTRAINT_TREE expression May 3, 2011 at 16:58
  • I stand corrected; I failed to take into account the LEFT OUTER JOIN-edness of the CTE. But there can be tables with no constraints; audit-style tables commonly have neither required columns, unique constraints, primary keys, nor referential integrity constraints.
    – Adam Musch
    May 3, 2011 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.