This discussion is archived
7 Replies Latest reply: Oct 30, 2012 12:56 AM by 970863 RSS

sqlldr returns ORA-14400 although partition exists

970863 Newbie
Currently Being Moderated
Hi,

I have encountered an error with SQL Loader which I was hoping that someone here could help me out.

I am loading records from a file which have different length. Which type of record it is is identifiable by a field called record_type and all the records in the file are loaded into the same table.

The problem I encounter is that in average one record out of each 10000 is rejected with the errors:
ORA-00604: error occurred at recursive SQL level 1
ORA-14400: inserted partition key does not map to any partition

These records end up in a .bad file and I look at the date, which the partition is based on, then I don't find anything that can explain why it was rejected since the partition is there.

When I try to load the content .bad file into the table then it works without any of the previously rejected records being rejected again.

What I don't understand is why the records are rejected in the first place, but then at a second attempt successfully loaded although no modification has been done to the table (no new added, nor removed partitions). Am I somehow using the SQL loader in a way that it is not meant to be used, or might I have forgotten a setting in the .ctl file?

Oracle version is 11.1.0.7.0.

The control file I am using looks like this (subset, got altogether 6 different record types)
LOAD DATA
INFILE *
APPEND
INTO TABLE ROAM5
WHEN (41:43) = 'CA3'
TRAILING NULLCOLS
(
FILE_NAME POSITION(1:40) CHAR(40) "TRIM(:FILE_NAME)",
RECORD_TYPE POSITION(41:43) CHAR(3),
IMSI POSITION(44:58) CHAR(15) "TRIM(:IMSI)",
IMSI_TYPE POSITION(59:60) CHAR(2) "TRIM(:IMSI_TYPE)",
SENDER_ID POSITION(61:80) CHAR(20) "TRIM(:SENDER_ID)",
CUSTOMER_TAG POSITION(81:134) CHAR(54) "TRIM(:CUSTOMER_TAG)",
DATE_FROM_CDR POSITION(135:148) Date "YYYYMMDDHH24MISS",
CALL_EVENT_DATE POSITION(149:162) Date "YYYYMMDDHH24MISS",
TOTAL_CHARGED_UNITS POSITION(163:167) CHAR(5) "TRIM(:TOTAL_CHARGED_UNITS)",
FINAL_CHARGE_RATE POSITION(168:179) CHAR(12) "TRIM(:FINAL_CHARGE_RATE)",
TOTAL_CHARGEABLE_UNITS POSITION(180:184) CHAR(5) "TRIM(:TOTAL_CHARGEABLE_UNITS)",
CURRENCY_CODE POSITION(185:189) CHAR(5) "TRIM(:CURRENCY_CODE)",
USAGE_TYPE POSITION(190:194) CHAR(5) "TRIM(:USAGE_TYPE)",
TAX_RATE POSITION(195:206) CHAR(12) "TRIM(:TAX_RATE)",
CAMEL_SERVICE_LEVEL POSITION(207:207) CHAR(1) "TRIM(:CAMEL_SERVICE_LEVEL)",
CALLED_NUMBER POSITION(208:228) CHAR(21) "TRIM(:CALLED_NUMBER)",
ANNOTATION POSITION(229:483) CHAR(255) "TRIM(:ANNOTATION)",
CALLING_NUMBER POSITION(484:488) CHAR(5) "TRIM(:CALLING_NUMBER)"
)

INTO TABLE ROAM5
WHEN (41:43) = 'GC3'
TRAILING NULLCOLS
(
FILE_NAME POSITION(1:40) CHAR(40) "TRIM(:FILE_NAME)",
RECORD_TYPE POSITION(41:43) CHAR(3),
IMSI POSITION(44:58) CHAR(15) "TRIM(:IMSI)",
IMSI_TYPE POSITION(59:60) CHAR(2) "TRIM(:IMSI_TYPE)",
SENDER_ID POSITION(61:80) CHAR(20) "TRIM(:SENDER_ID)",
CUSTOMER_TAG POSITION(81:159) CHAR(79) "TRIM(:CUSTOMER_TAG)",
CALLING_NUMBER POSITION(160:164) CHAR(5) "TRIM(:CALLING_NUMBER)",
DATE_FROM_CDR POSITION(165:178) Date "YYYYMMDDHH24MISS",
CALL_EVENT_DATE POSITION(179:192) Date "YYYYMMDDHH24MISS",
DATE_FROM_CDR POSITION(165:178) Date "YYYYMMDDHH24MISS",
CALL_EVENT_DATE POSITION(179:192) Date "YYYYMMDDHH24MISS",
TOTAL_CHARGED_UNITS POSITION(193:204) CHAR(12) "TRIM(:TOTAL_CHARGED_UNITS)",
FINAL_CHARGE_RATE POSITION(205:216) CHAR(12) "TRIM(:FINAL_CHARGE_RATE)",
TOTAL_CHARGEABLE_UNITS POSITION(217:228) CHAR(12) "TRIM(:TOTAL_CHARGEABLE_UNITS)",
TAX_RATE POSITION(229:240) CHAR(12) "TRIM(:TAX_RATE)",
CURRENCY_CODE POSITION(241:245) CHAR(5) "TRIM(:CURRENCY_CODE)",
USAGE_TYPE POSITION(246:250) CHAR(5) "TRIM(:USAGE_TYPE)",
ANNOTATION POSITION(251:442) CHAR(192) "TRIM(:ANNOTATION)",
APN_NI POSITION(443:447) CHAR(5) "TRIM(:APN_NI)"
)

The partitioning is based around a syntax like this:
)
TABLESPACE REVASS_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (DATE_FROM_CDR)
(
PARTITION CDR_20121014 VALUES LESS THAN (TO_DATE(' 2012-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS

I am thankful for any hint or help that you could give me.

Best regards,
Jesper

Edited by: 967860 on Oct 26, 2012 4:10 AM - Added Oracle version
  • 1. Re: sqlldr returns ORA-14400 although partition exists
    User286067 Journeyer
    Currently Being Moderated
    967860 wrote:
    PARTITION BY RANGE (DATE_FROM_CDR)
    ( PARTITION CDR_20121014 VALUES LESS THAN (TO_DATE(' 2012-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    So, you sure you have no data in the source file that has date_from_cdr later than Oct 15th midnight? Since I dont see a MAXVALUE partition, I presume that would be primary cause of error.
  • 2. Re: sqlldr returns ORA-14400 although partition exists
    970863 Newbie
    Currently Being Moderated
    Hi,

    Sorry for not being clear about the amount of partitions. There are partitions created up until today + 10 days, i.e. 5th of November. So any later events should be loaded into those partitions.

    The problem here is that the loading fails the first time, but the reload of the events in the .bad file works even if it is run directly after the first load.

    Best regards,
    Jesper
  • 3. Re: sqlldr returns ORA-14400 although partition exists
    User286067 Journeyer
    Currently Being Moderated
    So can you show us the contents (few lines) of badfile from failed records ?
  • 4. Re: sqlldr returns ORA-14400 although partition exists
    970863 Newbie
    Currently Being Moderated
    Of course, here is one GC3 record which failed this morning:
    CDDEUM3CHEM208006.dat GC3228123456789123 6DEUM30800600011331012672649480101111593 7
    7 DEUM3201210281252372012102812523712288 0 11378 1 37530 41791234567 10.128.128.128
    +0100 F 3 4 13070002469860 20121028125237+0100X+0100 0 internet

    Which I would have expected to have been loaded into this partition in the ROAM5 table:
    PARTITION CDR_20121028 VALUES LESS THAN (TO_DATE(' 2012-10-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE REVASS_DATA
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 80K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    BUFFER_POOL DEFAULT
    ),

    The record:
    CDFRAM3CHEM217542.dat GC3228123456789123 6FRAM3175420000197801328900564420996812 1
    14 FRAM3201210241700532012102417005312288 6 11342 1 37530 41761234567 10.128.128.128
    3 4 012841007464311620121024170053+0200X+0200 0 internet


    Should have ended up in this partition:
    PARTITION CDR_20121024 VALUES LESS THAN (TO_DATE(' 2012-10-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS
    TABLESPACE REVASS_DATA
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 80K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    BUFFER_POOL DEFAULT
    ),

    Best regards,
    Jesper

    Edited by: Jesper967860 on Oct 29, 2012 12:55 AM

    Edited by: Jesper967860 on Oct 29, 2012 12:59 AM
  • 5. Re: sqlldr returns ORA-14400 although partition exists
    User286067 Journeyer
    Currently Being Moderated
    Of you are running in direct path load (direct=y) then check Bug 8611203
  • 6. Re: sqlldr returns ORA-14400 although partition exists
    970863 Newbie
    Currently Being Moderated
    Hi rjamya,

    I'll check it up and let you know what I find. Thanks for the hint.

    Best regards,
    Jesper
  • 7. Re: sqlldr returns ORA-14400 although partition exists
    970863 Newbie
    Currently Being Moderated
    Hi rjamya,

    You were right about the direct path load. I removed it and then have let the load run for 17-18hrs without encountering these errors again.

    Many thanks for your help and support.

    Best regards,
    Jesper

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points