SQL*Loader stuck after loading 4.2 billion records

Question

We are stuck with a problem in sql loader. We are trying to load a data file with around 4.6 billion rows (nearly 340 GB) into 2 oracle tables on the basis of some when condition using Sql Loader. But after loading 4.2 billion records the SQL loader process is getting completed without throwing any errors even when rest of the records are still to be loaded.

There are no dicarded or bad records as well. Is there any limit for the number of records SQL Loader can load? Could not find any such thing documented anywhere. Please let me know if anyone has any clue for this issue.

Thanks!!

Ariadne

I'm also facing this, except for me it doesn't complete, it keeps printing "logical record count 4290000000" every second. Probably due to the fact that i have the ROWS setting set to 10000000. Seems like it keeps retrying to insert the next 10000000 records. Also, there is still network activity, indicating it keeps retrying. – Airla

Answer 1

The value of 2³² is 4294967295 which is close to 4.2 billion.

So from the description of your problem I could only say that maybe sqlldr's default value for LOAD is actually 2³² - 1, and this value is stored on 32 bits.

Sandusky

You're a genious. This makes perfect sense in combination with the issue i'm having. Since it can insert up to 4290000000, but not up to 4300000000. So, does anyone know a way around this? Does Oracle know about this? – Airla

Answer 2

I've tested the hypothesis of Benoit about the internal LOAD integer being too small for +4.2bil rows. It holds true. When cutting up my source file into files of -4.2bil rows, and inserting them using append, the insert works perfectly.

So, it's not a database limitation, as suggested here: https://forums.oracle.com/message/11201322 (also posted my conclusions there)

Also, there's the suggestion of BluShadow on the Oracle Forum to try and use External tables. Didn't test that yet.

Airla