Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 5:04
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
One nit: a table that is relationally neither a parent nor a child will not be captured by that script. –  Adam Musch May 3 '11 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 –  Justin Cave May 3 '11 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 '11 at 19:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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