How to list tables in their dependency order in oracle (based on foreign keys)?-CodePudding

Background I wanted to migrate data with insert operation on multiple tables. The tables have foreign key relationships between themselves. If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.

Requirement Produce a list of tables within a database ordered according to their dependencies. Tables with no dependencies (no foreign keys) will be 1st. Tables with dependencies only in the 1st set of tables will be 2nd. Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd. and so on...

CodePudding user response：

From my point of view, that's wrong approach.

There's utility which takes care about your problem, and its name is Data Pump.

export (expdp) will export everything you want
import (impdp) will then import data, taking care about foreign keys

I suggest you use it.

If you don't want to (though, I can't imagine why), then consider

creating target tables with no referential integrity constraints (i.e. no foreign keys)
run your insert statements - all of them should succeed (at least, regarding foreign keys; can't tell about other constraints)
- note that this is really, really slow ... insert runs one-row-per-statement so, if there's a lot of data, it'll take time
once you're done with inserts, enable (or create) foreign key constraints - all of them should succeed as there's parent for every child (presuming that none of inserts failed)

CodePudding user response：

If you still want to know FK dependencies for other purpose:

SELECT master_table.table_name  master_table_name,
       master_table.column_name master_key_column,
       detail_table.table_name  detail_table_name,
       detail_table.column_name detail_column
  FROM all_constraints  constraint_info
  JOIN
        all_cons_columns detail_table ON constraint_info.constraint_name = detail_table.constraint_name
  JOIN
        all_cons_columns master_table ON constraint_info.r_constraint_name = master_table.constraint_name
 WHERE 
   detail_table.position = master_table.position
   AND constraint_info.constraint_type = 'R'
   AND constraint_info.owner = 'SCHEMA_OWNER'

From there, you can do a recursive query starting with detail_table_name not in the master_table_name list and connecting by prior master_table_name = detail_table_name.