Thanks for your reply. Unfortunately, the loader's log file was overwritten before our developer called me since she tried to rerun the job.
Dave
From: "Goulet, Dick" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: unique index Date: Tue, 21 Oct 2003 08:44:32 -0800
Dave,
If memory is functioning normally: When you use direct=y in Sql*Loader it flags all of your indexes as invalid and then revalidates/rebuilds then when the load is complete. The reason is that loading data is faster when you don't have to parse index entries all the time and an invalid index does not need to be maintained. It would appear from your message that something caused the one index to not validate during the Sql*Loader run. Why might be revealed in the loader's log file.
Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA
-----Original Message----- Sent: Tuesday, October 21, 2003 12:29 PM To: Multiple recipients of list ORACLE-L
Hi List,
We have a job that appends records to a table using SQL Loader
(DIRECT=TRUE). The table has two unique indexes (no constraints). Last
Sunday, the job loaded 11839 records into the table successfully, but the
one of the unique indexes became unusable for unknown reason. I dropped the
unusable index and recreated it. The index became valid. Then the
developer reran the job and loaded the same 11839 records into the table (at
that time we did not know the first run already loaded the records). Of
course, two unique indexes became unusable again. I could not recreate the
unique indexes due to the duplicate keys found. Finally, I deleted all of
23678 newly loaded records, recreated the unique indexes, and reloaded the
11839 records. Every thing is fine now. Here are my questions:
1. Why the same data crashed the index at the first time, but not at the end
2. After I recreated the unique index at the first time, those records were
already in the table. Why did not the unique index complain for the
duplicates when we reloaded the same 11839 records into the table?
Dave
_________________________________________________________________ Send and receive larger attachments with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
_________________________________________________________________
Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
