CODEWITHSUNDEEP
oracle10g
Padmanabha Vn
Padmanabha Vn

Reputation: 624

populate test data to oracle database

I have a oracle database schema with more complicated foreign key relation.. I need to populate test data to all the tables.. due to foreign key constraints i am finding it difficult to find hierarchy of tables.. can anyone suggest any package or method to accomplish this..

Thanks in advance

Upvotes: 1

Views: 1305

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

If the hierarchy of tables is very complicated, and if you can get sole access to the schema (i.e. impose some "down time" on the users), you could disable all the foreign key constraints, load the data, then re-enable the constraints.

Another alternative is to use deferrable constraints, and only defer them for the session that is loading the data; but there are disadvantages to this, one being that you'd first have to drop all the constraints in order to make them deferrable if they're not already.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231801

It would be helpful if you could let us know what form you want the output to take. You may want to start with Frank Kulash's example of a hierarchial query against the DBA_CONSTRAINTS table to show the path.

If you are looking for a way to determine what order to load tables, that's identical to a question that was asked on dba.stackexchange (can't mark this question as a duplicate because DBA is still in beta). 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.

Upvotes: 6

Related Questions