This content has been marked as final. Show 3 replies
The Oracle database's components are backward compatible ... not forward compatible.
Use the 11gR2 tool with the 10g database ... not the other way around.
This may not be the specific cause of your issue but it is certainly a starting point.
PS: 10.2.0.1? What happened to patching during the last decade?
Thank you for your answer. I tried the same SQL*Loader data loading process from my PC with Oracle Client 11.2 and with the same data file, control file and the same database, and I got the same error:
I noticed that number for "Load completed - logical record count" is not always same. For example, when data file size is around 300 MB the logical record count is 45000 and when the data file size is 500 MB then logical record count is 110000. The data in both files is of the same nature, just vehicles GPS locations (points).
SQL*Loader: Release 184.108.40.206.0 - Production on Fri Oct 12 10:17:57 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ORA-39776: fatal Direct Path API error loading table CISDWSYS.VEHICLE_LOCATION_FACT ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-22054: underflow error ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225 SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. Load completed - logical record count 45000.
The control.log file is
Version 10.2.0.1 is on the machine that I got to use to provide a proof-of-concept solution. Long story, I can't install or upgrade clients on the machine.
SQL*Loader: Release 220.127.116.11.0 - Production on Fri Oct 12 10:17:57 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: Load_Vehicle_Locations_2.ctl Data File: CIS_VehLoc_L05120603gps.txt Bad File: CIS_VehLoc_L05120603gps.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: Direct Table VEHICLE_LOCATION_FACT, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- VEHICLE_LOCATION_MESSAGE_ID CONSTANT Value is '222' MESSAGE_DATETIME FIRST * , DATE YYYY-MM-DD HH24:MI:SS ROUTE_NUMBER NEXT * , CHARACTER RUN_NUMBER NEXT * , CHARACTER VEHICLE_NUMBER NEXT * , CHARACTER BADGE_NUMBER NEXT * , CHARACTER TRIP_DIRECTION NEXT * , CHARACTER GPS_LONGITUDE_NUMBER NEXT * , CHARACTER GPS_LATITUDE_NUMBER NEXT * , CHARACTER DIVISION_NUMBER NEXT * , CHARACTER VEHICLE_LOCATION DERIVED * COLUMN OBJECT *** Fields in VEHICLE_LOCATION SDO_GTYPE NEXT * , CHARACTER SDO_SRID CONSTANT Value is '8265' SDO_POINT DERIVED * COLUMN OBJECT *** Fields in VEHICLE_LOCATION.SDO_POINT X NEXT * , CHARACTER Y NEXT * , CHARACTER *** End of fields in VEHICLE_LOCATION.SDO_POINT *** End of fields in VEHICLE_LOCATION ORA-39776: fatal Direct Path API error loading table CISDWSYS.VEHICLE_LOCATION_FACT ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-22054: underflow error ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 720 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225 SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. Table VEHICLE_LOCATION_FACT: 0 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Date cache: Max Size: 1000 Entries : 192 Hits : 44808 Misses : 0 Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes:20971520 Total logical records skipped: 0 Total logical records rejected: 0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 9 Total stream buffers loaded by SQL*Loader load thread: 9 Run began on Fri Oct 12 10:17:57 2012 Run ended on Fri Oct 12 10:18:34 2012 Elapsed time was: 00:00:37.16 CPU time was: 00:00:00.32
I researched this on internet and I can't find anything on this combination of "failed ODCIINDEXINSERT routine" and "underflow error". The data values are good, without the spatial index present the SQL*Loader loads the same data ok and the spatial index is recreated without problems.
An Oracle document says that SQL*Loader direct path loading can't be used on a table with a spatial index (domain index), because spatial index is defined on a column with spatial data type and that is LOB type of column.
The document is "Oracle® Database Data Cartridge Developer's Guide 11g Release 2 (11.2)" and the link is:
Paragraph from this book about this data loading limitation is:
Domain Indexes and SQL Loader
SQL*Loader conventional path loads and direct path loads are supported for tables on which domain indexes are defined, with two limitations:
•The table must be heap-organized.
•The domain index cannot be defined on a LOB column.
To do a direct path load on a domain index defined on an IOT or on a LOB column, perform these tasks:
1.Drop the domain index
2.Do the direct path load in SQL*Loader.
3.Re-create the domain indexes.
A solution in my case is to create daily partitions for this huge table with spatial (points) locations, since re-create spatial index partition in this case takes about 15 minutes and this is ok. That's much better than re-creating spatial index monthly partition which takes hours, or loading millions of records without direct path method (and no need to drop spatial index partition) which takes even more hours. Our process is based on daily data loading anyway, so it is ok for us.