sqlldr stopping
574505Aug 8 2008 — edited Aug 22 2008I have a very large data set (roughly 7 billion rows) I am trying to load into Oracle 11g on linux x86_64. I need it to load as fast as possible.
I am loading the data using sqlldr. My sqlldr command line is:
sqlldr DIRECT=TRUE ROWS=1000000 userid=ptest/password control=gtypes.ctl
My control file looks like this:
LOAD DATA INFILE '/root/testcode/gtest.out' TRUNCATE INTO TABLE gtypes FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'' TRAILING NULLCOLS (patient_id,gtype_code,gtype)
My input file looks like this:
.
.
.
7000,999991,4
7000,999992,4
7000,999993,4
7000,999994,3
7000,999995,3
7000,999996,5
7000,999997,1
7000,999998,3
7000,999999,3
7000,1000000,5
(for a total of roughly 7 billion of these records)
If I load it without the DIRECT=Y it works but is much slower. When I load it with DIRECT=Y, it stops part of the way through...
Save data point reached - logical record count 4294000000.
Save data point reached - logical record count 4294000000.
Save data point reached - logical record count 4294000000.
Save data point reached - logical record count 4294000000.
Save data point reached - logical record count 4294000000.
Coincidentally, this is roughly the size of a 32 bit number. Not sure if that has anything to do with it, but I really cannot figure out why it is stopping at this point. There is nothing in the logs, no warnings. I have plenty of diskspace free, both the table itself and the associated temp table are set to grow automatically as needed. When the tables are created they have no indexes, so it's not an index issue.
Any thoughts?
Marc Bouffard