All geek questions in one place

SQL * Loader stuck after loading 4.2 billion records

We encountered a problem in the sql loader. We are trying to load a data file with approximately 4.6 billion rows (about 340 GB) into 2 oracle tables based on some, provided that Sql Loader is used. But after loading 4.2 billion records, the SQL loader process terminates without any errors, even if the rest of the records still need to be loaded.

There are also no records of failed or bad records. Is there a limit on the number of records loaded by SQL Loader? Could not find such a document anywhere. Please let me know if someone prompts this problem.

Thanks!!

+6
source share
2 answers

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

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

+4
source

I tested the Benoit hypothesis that the internal LOAD integer is too small for + 4.2bil strings. It's right. When you cut the source file into 4.2 bit files and paste them using append, the paste works fine.

So, this is not a database limitation, as suggested here: https://forums.oracle.com/message/11201322 (also there were my findings)

There is also a BluShadow suggestion on the Oracle forum to try and use external tables. I have not tested it yet.

+1
source

Source: https://habr.com/ru/post/895303/

More articles:


All Articles