Re: SQLLOADER fails

I had a similar problem but I traced it down to missing indexes. i.e. It hadn't locked up it was just running very, very slowly. I don't know if this problem would show itself when using direct load. I proved it by changing the commit to 1. It took 1 minute to insert one row because it was doing a table scan due to foreign key lookups.

Richard Murphy wrote:

> Have you tried it without the direct path option. There are some limitations on
> direct path that depend on the table set up. Also try reducing the error amount
> to a reasonable size (just a few for testing purposes) so that you can look at
> the log file after a few errors instead of the whole table - the log file
> contains a record for each discarded record. This is a typical sample that i
> use:
>
> OPTIONS (BINDSIZE=10000000 , ROWS=100, ERRORS=150)
> LOAD
> TRUNCATE
> INTO TABLE TABLE_NAME
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> ( ...
> )
>
> I usually pass the input data file in on the command line for scripting purposes:
>
> sqlldr name/password cfile.ctl data=infile.dat
>
> Hope this helps.
> R.
>
> andrea_johnson_at_wolfe.net wrote:
>
> > ORACLE 8.04/solaris
> >
> > I'm really at a loss. Please give me some in depth feedback because I do not
> > understand why this seemingly simple load fails.
> >
> > I'm trying to load some 18,000 records.
> >
> > The .dat file is tab delimited
> >
> > Using direct=true
> >
> > The process starts and then just hangs--never completing--I've been killing
> > the process manually through the O/S.
> >
> > I've removed the table and recreated it without an index--thinking it was the
> > sorting that was causing the problem. At this point the table is empty--but
> > eventually I'll need to replace the data with future loads.
> >
> > WHAT AM I DOING WRONG! Where can I look to see what I've messed
> > up/configured incorrectly...(thanks in advance)
> >
> > This is my .ctl file:
> >
> > LOAD DATA
> > INFILE '/oracle/or0/SQL-load-files/customermaster/CLIENTS-CONVERT.dat'
> > TRUNCATE
> > DISCARDMAX 18000
> > INTO TABLE pub.customermaster
> > FIELDS TERMINATED BY X'09'
> > ( account
> > ,email
> > ,lastName
> > ,firstName
> > ,companyName NULLIF companyName=BLANKS
> > ,billTo NULLIF billTo=BLANKS
> > ,billAddress
> > ,billCity NULLIF billcity=BLANKS
> > ,billState NULLIF billState=BLANKS
> > ,billZip NULLIF billzip=BLANKS
> > ,Address
> > ,City NULLIF City=BLANKS
> > ,State NULLIF State=BLANKS
> > ,Zip NULLIF Zip=BLANKS
> > ,dayPhone
> > ,evePhone NULLIF evePhone=BLANKS
> > ,fax NULLIF fax=BLANKS
> > ,preferPhone NULLIF preferPhone=BLANKS
> > ,billNotify NULLIF billNotify=BLANKS
> > ,paymentMethod NULLIF paymentMethod=BLANKS
> > ,platform NULLIF platform=BLANKS
> > ,homePOP
> > ,source NULLIF source=BLANKS
> > ,activationdate DATE "yyyymmdd" NULLIF activationdate=BLANKS
> > ,comments NULLIF comments=BLANKS
> > ,salesperson NULLIF salesperson=BLANKS
> > ,doneBy
> > ,deactivationdate DATE "yyyymmdd" NULLIF deactivationdate=BLANKS
> > ,lastProvider NULLIF lastProvider=BLANKS
> > ,socSec NULLIF socSec=BLANKS
> > ,birthDate DATE "yyyymmdd" NULLIF birthDate=BLANKS
> > ,billlastname NULLIF billlastname=BLANKS
> > ,billfirstname NULLIF billfirstname=BLANKS
> > )
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Mar 30 1999 - 03:44:00 CST