1

I try to insert 100 millions records of 18 fields on scv file(15G) in oracle by sql loader but 89 million records inserted and others not without any errors in log. I set all field to char type in sql loader file control and oracle tables to handle conflict data type. this my clt file

option (skip=1, parallel=true, errors=5000)
load data
characterset  UTF8
infile 'file.scv'
append
into table table_name
fields terminated by ','
trailling nullcols(
name char(200),
family char(200)
...
)

sql loader command is

sqlldr userid=user/pas@ip:port/sid 
DATA=file_name.csv table=table_name log=log_file_name.log

and my table space options are, size 77.2G, auto extend true, max size unlimited. why this happen? is it about memory capacity or other options in oracle or sql loader?

Improve this question

2 Answers 2

Reset to default
2

Well, it is you who instructed SQL*Loader to do that.

option (skip=1, parallel=true, errors=5000)
                               -----------

Documentation says:

ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.

You allowed 5000. What happened then? As Oracle says, SQL*Loader terminated the load.

Check the .BAD file for more info.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thanx. my problem is that csv file has a end of file character middle of file and sql* loader stop reading file.
@r.a.shehni - WHY does your file have an end-of-file character in the middle of the file? If you can find it, remove it (unless it is there for a good reason).
unfortunately, I do not create csv file so I think this a mistake in create. I test file with a python code and found that.
0

The (.bad) file shows the records that were not imported, and the (.log) file shows why.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.