Parent-Child

[st2000]

I am going to create flatfiles from my db using dbms_util file package. Once I create them I am going to create tables and dump them into tables using Sql loader.. I will definitely have problems with constraints and so on..
Is there any efficient way to find out all children tables first and then parent tables.. so that I can load sequentially..
The reason why I am doing this is basically, be able to alter the structure (as a part of upgrade) if any and then load data.
Thanks..

[jm]

you don't have to worry about the child/parents if
disable constraint first, load, then enable constraints.

[dragon99]

The following script will let you find out parent table and child tables.

Cheers


set echo off
set verify off
col TABLE_NAME format a30
col COLUMN_NAME format a20
col POSITION format a30

accept xTable prompt 'Enter Table Name: '
TTITLE LEFT 'Child Tables for the table: '&xTABLE
break on TABLE_NAME
SELECT B.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND A.TABLE_NAME = UPPER('&xTable')
ORDER BY B.TABLE_NAME, C.POSITION;
TTITLE LEFT 'Parent tables for the table: '&xTable
SELECT A.TABLE_NAME, C.COLUMN_NAME, C.POSITION
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND B.TABLE_NAME = C.TABLE_NAME
AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND B.TABLE_NAME = UPPER('&xTable')
ORDER BY A.TABLE_NAME, C.POSITION;