| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sqlldr questions
FYI:
be careful trusting sqlldr will reject all that it should.
I had some big problems using 8.1.5 on aix, and once reported, support
admited that it
reproduces in 8.1.6 but...thank god... it's fixed in 8.1.7 (they said so...)
I got duplicates primary keys inserted, non-existing foreign keys, and lots
of
"no data found" errors, with no more explanation.
The problem, in my case, was a before insert or update trigger in the tables
where
sqlldr was appending, after the first ofending record, anything could
happen.
I really got an inconsistent db with foreign key violated and so on.
The work around in this case was: drop the trigger or load with rows=1,
which
implies a commit for each record, and in your case, could be so bad....
HTH
Gabriel
Gabriel Galanternik
Tesis OyS
Charlie,
You cannot do a sanity check - there is no such thing as a dry sqlldr run. In fact, it depends on how dirty your data is. Dirty rows go to the .bad file. If your data is just mildly dirty (i.e. invalid dates, duplicate keys ...) it's pretty easy to edit the .bad file, correct what needs to be corrected, and use a control file similar to the original one but for two things :
o load in APPEND mode
o use the (corrected and renamed) .bad file as input file.
The unpleasant case is when your data contains the character(s) used as separators here and there, or cariiage returns while you have not specified anything for continuation rows, because then SQL*Loader is likely to load correctly partial rows and log partial rows to the .bad file too, which means that the clean-up will be painful (in other words, the 'good' case is when your .bad file contains full records). Unfortunately there is no easy way out I know. Concerning logging, use the DIRECT mode. It sometimes fails, but it will allow you to get back home (much) earlier.
-- Regards, Stephane Faroult email: sfaroult_at_oriolecorp.com Oriole Corporation Voice: +44 (0) 7050-696-269 Fax: +44 (0) 7050-696-449 Performance Tools & Free Scripts ------------------------------------------------------------------ http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs ------------------------------------------------------------------Received on Mon Oct 30 2000 - 13:20:48 CST
>
>
> I need to use sqlldr to load about 32GB worth of data
> as a one-shot deal. I ran an initial test over this
> last weekend. The test revealed some "dirty" data.
>
> Can sqlldr be configured such that it simply does a
> sanity test on the data to verify that all the records
> are in acceptable format WITHOUT actually going thru
> the overhead of loading the data? If so, how?
>
> I'm running the instance in archive mode. The test
> load job consumed all the disk space where the redo
> logfiles get deposited. Is there any way to configure
> sqlldr so that logging is DISBALED? If so, how?
>
>
> --
> Charlie Mengler Maintenance Warehouse
> charliem_at_mwh.com 10641 Scripps Summit Ct
> 858-831-2229 San Diego, CA 92131
 |
 |