Home » RDBMS Server » Server Utilities » SQL Loader - Failure (Oracle 11g)
SQL Loader - Failure [message #640058] Tue, 21 July 2015 11:30 Go to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Hi,

When my nightly job try to execute SQL* Loader to process a file which has 450K records, only 40K records are getting processed. But there are no errors or bad records mentioned in the SQL* Loader log files.

If i try to process the same file again on the next day, it processes all the 450K records successfully. I am facing this issue everyday and couldn't able to identify what is the root cause.

sqlldr parfile=$HOME/.dlgetpw/dlpassword/.getpw/parfile.par errors=10000 control="$home/control/cbusi.ctl" data="$home/data/pr.utf8" log="$home/logs/cbusi.log" bad="$home/logs/cbusi_BAD.txt" direct=true
Re: SQL Loader - Failure [message #640059 is a reply to message #640058] Tue, 21 July 2015 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you have a mystery & we have no clues?
is log file being overwritten which wipes out the evidence of failure?

when was the last time the nightly job worked with out error?
What changed since then?
Re: SQL Loader - Failure [message #640066 is a reply to message #640059] Tue, 21 July 2015 13:01 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Hi,

Log file will be overwritten, but before that i have captured the logs. Please see below logs for same file,

I am seeing this error for the past 3 days, nothing has changed in it for more than months.

Not Working

  45824 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         45824
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:       15
Total stream buffers loaded by SQL*Loader load thread:       14


Working

 404691 Rows successfully loaded.
  4 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:        404695
Total logical records rejected:         4
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      122
Total stream buffers loaded by SQL*Loader load thread:      127

Re: SQL Loader - Failure [message #640074 is a reply to message #640066] Tue, 21 July 2015 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tend to trust Oracle for such thing, so either your file had only 45824 either it contained an EOF character.

Re: SQL Loader - Failure [message #640077 is a reply to message #640074] Tue, 21 July 2015 15:42 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Michel,

If i process the same file again, it gets processed with all the 404695 records. This is what confusing.
Re: SQL Loader - Failure [message #640078 is a reply to message #640077] Tue, 21 July 2015 15:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
same input + same process = same results
?????input + same process = different results => the only logical conclusion is that the input is different contrary to anything you report
Re: SQL Loader - Failure [message #640153 is a reply to message #640078] Thu, 23 July 2015 02:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
What happens if you use direct=false instead of direct=true?

My first guess would be that whatever method you are using to run it as a scheduled job has some size limitation. Are you using dbms_scheduler or scheduling it from the operating system as a cron job (what operating system) or what?
Re: SQL Loader - Failure [message #640164 is a reply to message #640058] Thu, 23 July 2015 03:32 Go to previous message
gazzag
Messages: 1103
Registered: November 2010
Location: Bedwas, UK
Senior Member
What is your O/S?
sqlldr parfile=$HOME/.dlgetpw/dlpassword/.getpw/parfile.par errors=10000 control="$home/control/cbusi.ctl" data="$home/data/pr.utf8" log="$home/logs/cbusi.log" bad="$home/logs/cbusi_BAD.txt" direct=true

How does "$HOME" compare to "$home"?
Previous Topic: how to import user with network link with limitation rights
Next Topic: How to find which backup was used to import the db
Goto Forum:
  


Current Time: Sat Nov 27 12:16:52 CST 2021