hierarchical query in oracle

Question

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.

Answer 1

To do that you could try:

select
   level,
   owner, name,
   referenced_owner, referenced_name
from
   dba_dependencies
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.

Answer 2

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

WITH    constraint_tree AS
(
    SELECT DISTINCT
        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.