DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQLLDR - RENABLE DISABLED_CONSTRAINTS

  1. #1
    Join Date
    Aug 2002
    Posts
    2
    I'm running a SQL*Loader CTL script that is reading a fixed position ASCII file into multiple tables using the Direct Load method. Everything is working great except for the fact that all of my foreign key constraints are being disabled.

    I've added the clause "REENABLE DISABLED_CONSTRAINTS EXCEPTIONS myexcepttable" so that it will reenable the constraints at the end, but it doesn't appear to be reenabling the constraints. Additionally, the documentation says that the log file will indicate which constraints have been disabled and reenabled, but my log file isn't reporting anything related to the constraints.

    BTW..I'm running Oracle 8.1.6.

    Anyone have any ideas?

    Chris

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Problem Description:
    ====================
    You are going to perform a direct load and would like to use the REENABLE clause for disabled constraints.

    You are not sure how to build an exceptions table in order to use that clause.
    Search Words: =============
    SQL*Loader, SQLLOADER Solution

    Description: =====================

    Here is an example on how to create an exceptions table:
    create table dept2
    (deptno char(4) primary key check (DEPTNO < 22),
    dname char(20), loc char(20));

    CREATING THE EXCEPTIONS TABLE:
    create table except_table
    (row_id char(18), owner char(20),
    tablename char(20), constraint char(15));

    SQL> select constraint_name, constraint_type, search_condition, status from dba_constraints where table_name = 'DEPT2';

    CONSTRAINT_NAME C SEARCH_CONDITION STATUS
    SYS_C001737 P ENABLED
    SYS_C001738 C DEPTNO < 22 ENABLED

    SAMPLE CONTROL FILE:
    LOAD DATA INFILE *
    TRUNCATE INTO TABLE DEPT2
    REENABLE DISABLED_CONSTRAINTS EXCEPTIONS EXCEPT_TABLE
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    (DEPTNO , DNAME, LOC)
    BEGINDATA
    12,RESEARCH,"SARATOGA"
    10,"ACCOUNTING",CLEVELAND
    11,"ART",SALEM
    13,FINANCE,"BOSTON"
    21,"SALES",PHILA.
    22,"SALES",ROCHESTER
    42,"INT'L","SAN FRAN"

    STATUS OF CONSTRAINTS AFTER LOAD:

    CONSTRAINT_NAME C SEARCH_CONDITION STATUS
    SYS_C001737 P ENABLED
    SYS_C001738 C DEPTNO < 22 DISABLED

    SQL> select rowid, deptno, dname, loc from dept2;
    ROWID DEPT DNAME LOC
    00000071.0000.0005 12 RESEARCH SARATOGA
    00000071.0001.0005 10 ACCOUNTING CLEVELAND
    00000071.0002.0005 11 ART SALEM
    00000071.0003.0005 13 FINANCE BOSTON
    00000071.0004.0005 21 SALES PHILA.
    00000071.0005.0005 22 SALES ROCHESTER
    00000071.0006.0005 42 INT'L SAN FRAN

    SQL> select * from EXCEPT_TABLE;

    ROW_ID OWNER TABLENAME CONSTRAINT
    00000071.0005.0005 BLS DEPT2 SYS_C001738 00000071.0006.0005 BLS DEPT2 SYS_C001738

    In this case the exceptions table has a listing of the rows that have violated the check constraint. The you will need to manually delete the rows and reenable the constraint.

    For example:

    SQL> DELETE FROM DEPT2 WHERE ROWID = '00000071.0005.0005' AND ROWID = '00000071.0006.0005';

    SQL> ALTER TABLE DEPT2 ENABLE CONSTRAINT 'SYS_C001738'
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

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