SQL*Loader stuck after loading 4.2 billion records

问题:

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!!

回答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.

回答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.