I have numerous text files(5000 files) which should be loaded into oracle table. Below is the oracle table DDL. It is taking more 2 hours to load the data. I am using Sql Loader to load these files into Oracle table. Could some one suggest if i could improve the performance of the sqlloader.

External file or SQL*Loader are different alternatives to accomplish the same task.
One way to handle many files using either approach would be to hard code a single filename & the use OS soft link to redirect to each of the files individually.

Lalit Kumar BMessages: 3123Registered: May 2013 Location: World Wide on the Web

Senior Member

nagaraju.ch wrote on Mon, 23 September 2013 01:53

I have numerous text files(5000 files) which should be loaded into oracle table. Below is the oracle table DDL. It is taking more 2 hours to load the data. I am using Sql Loader to load these files into Oracle table. Could some one suggest if i could improve the performance of the sqlloader.

You are using direct path load. You can do it more efficiently, try these parameters along with it -

parallel=true , multithreading=true , skip_index_maintenance=true

After setting those parameters, run the sqlldr and check the sqlldr log, you should see the following things -

It worked well. Half of the time is saved. But here is the problem when we use these Parallel and multithreading parameters, I use Sequence to generate unique numbers for every transaction. It is skipping this task. I mean nulls are inserting into that field.

Lalit Kumar BMessages: 3123Registered: May 2013 Location: World Wide on the Web

Senior Member

nagaraju.ch wrote on Tue, 24 September 2013 20:48

It worked well. Half of the time is saved. But here is the problem when we use these Parallel and multithreading parameters, I use Sequence to generate unique numbers for every transaction. It is skipping this task. I mean nulls are inserting into that field.

Can't we use Sequences with these parameters?

It is not a restriction with parameters Parallel and multithreading, it is a restriction on using Direct path load.

How does a SQL*Loader work in conventional path load? Answer is simple, SQL*Loader simply builds an insert statement to load the data into the table.

So, you cannot create sequences in direct path loads, since, there is no SQL being generated to fetch the next value since direct path does not generate INSERT statements.