Parent-Child
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Parent-Child

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    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..

  2. #2
    Join Date
    Nov 2000
    Posts
    245
    you don't have to worry about the child/parents if
    disable constraint first, load, then enable constraints.

  3. #3
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width