Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlldr stopping

574505Aug 8 2008 — edited Aug 22 2008
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2008
Added on Aug 8 2008
11 comments
1,092 views