oracle 29875,Failed in the execution of the ODCIINDEXINSERT routine while loading points-CSDN博客

I used SQL*Loader from 11.2 client to load million records into a 11.2 spatial table (points with longitude and latitude) and I got the following error:

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 12 10:17:57 2012

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 table has a spatial index on VEHICLE_LOCATION column with MDSYS.SDO_GEOMETRY data type. Both table and spatial index are partitioned “monthly” by MESSAGE_DATETIME column. The database is 11.2 (11gR2), the client and SQL*Loader is version 11.2.

My control file is:

options (direct=yes, bindsize=20971520, readsize=20971520)

Load data

Append

into table VEHICLE_LOCATION_FACT

fields terminated by ","

(VEHICLE_LOCATION_MESSAGE_ID CONSTANT 222,

MESSAGE_DATETIME Date 'YYYY-MM-DD HH24:MI:SS',

ROUTE_NUMBER,RUN_NUMBER,VEHICLE_NUMBER,BADGE_NUMBER,

TRIP_DIRECTION,GPS_LONGITUDE_NUMBER,GPS_LATITUDE_NUMBER,DIVISION_NUMBER,

VEHICLE_LOCATION COLUMN OBJECT

(

SDO_GTYPE Integer EXTERNAL,

SDO_SRID CONSTANT 8265,

SDO_POINT COLUMN OBJECT

( X FLOAT EXTERNAL,

Y FLOAT EXTERNAL

)

)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).

The control.log file is

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 12 10:17:57 2012

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

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.32I’m confused by this reported “underflow error” because all data in the data file are correct values. When I drop the spatial index and load the records by using the same control file, same data file and the same table, I don’t get any error and later I recreate the spatial index without errors. That should mean that the values in the data file are good, no “under” values.

Also, when I don't use "DIRECT=yes" loading in the control file, then SQL*Loader loads all records with no errors, but it takes hours which is too long.

I want to avoid to have to drop the spatial index every morning before the data loading process, because it is a very large table and it takes 45-60 minutes to recreate that index partition, and that much time delay is not good.

Can anyone please provide an advice or point to some link with examples of how to use “sqlldr” to load large number of records into a spatial table with spatial index?

Thank you,

Milan