I had an interesting problem today. One of the developers told me a nightly job had stopped with an error. I check the usual things like has anything changed and was told no. The error was sent to me and was the following:
SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table REPORT_TEMP
ORA-01502: index 'PK_REPORT_TEMP' or partition of such index is in unusable state
I looked at the index and found it to be unusable:
SQL> select index_name, uniqueness, status from dba_indexes where table_name = 'REPORT_TEMP';
INDEX_NAME UNIQUENESS STATUS
------------------------------ ------------------------------------ --------------------------------
PK_REPORT_TEMP UNIQUE UNUSABLE
1 row selected.
My first thoughts were to rebuild it:
SQL> alter index mercury.PK_REPORT_TEMP rebuild;
alter index mercury.PK_REPORT_TEMP rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
I check the table and found it had two duplicate rows. Next I checked the constraints on the table:
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME VALIDATED, INDEX_NAME, INVALID from dba_constraints where table_name = 'REPORT_TEMP'
2 /
CONSTRAINT_NAME CONS
------------------------------ ----
R_OWNER
------------------------------------------------------------------------------------------------------------------------
VALIDATED INDEX_NAME INVALID
------------------------------ ------------------------------ ----------------------------
PK_REPORT_TEMP P
PK_REPORT_TEMP
After checking the control file for sql*loader, I found that load was done via a DIRECT method. The job had worked the same way for quite a while by replacing the current rows with the new load (REPLACE) and then performing some extra processing.
As a conventional load converts the data into a bunch of INSERT statements any issues are written to the BAD file. The problem seemed to be using the DIRECT method to load and it's handling of constraints. A direct load mode by-passes the formatting of the data into INSERT statements and writes the data directly into the table.
"During a direct path load, new index keys for the new rows are written
to a temporary segment. At the end of the load, each
temporary segment is sorted, and then merged with the corresponding old
index in order to create a new index. After the new indexes are created,
the old indexes and the temporary segments are removed. Merging all the
new index entries at once like this usually requires less processing
overhead than would be required to insert each new index entry one at a
time." - http://www.ypl.com/oracle/sql_loader/gennick_jonathan/html_deep/ch10s03s06s01.html
"The PK and UNIQUE constraints remain enabled during a direct path load, but new rows
are only verified against these constraints at the end of the load
process when the underlying indexes are rebuilt. If a row violates a
UNIQUE KEY or PRIMARY KEY constraint, then it is not rejected; it still
gets loaded. However, the corresponding index will remain in the
UNUSABLE state after the load completes. You need to locate the
duplicate row(s), resolve the problem, and then rebuild the index
manually." - http://www.ypl.com/oracle/sql_loader/gennick_jonathan/html_deep/ch10s03s07.html
I had to remove the duplicate rows and I couldn't just run a DELETE FROM command as the index was unusable. I did try disabling the PK index and running the DELETE FROM again but it failed. I was going to drop the PK index but as this was a production issue and I ran out of time I did a TRUNCATE TABLE (the data was temporary and we could rerun the load). The TRUNCATE worked. I changed the sql*loader method from DIRECT to CONVENTIONAL and the duplicates in the source data went in to the bad file and the load worked.
I guess the interesting thing is the effected DIRECT loading has. To stop this happening again we had two options
1. Change the sql*loader method permanently the draw back to doing this is speed. DIRECT is very fast as a conventional path load executes SQL INSERT
statements to populate tables in an Oracle database.
2. Change source data to not have duplicates (sort -u in Linux)
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteشركة المثالية لمكافحة الحشرات بالدمام
ReplyDeleteشركة نقل اثاث من الرياض الى الامارات
ReplyDeleteشركة شحن اثاث من الرياض الى الامارات
ReplyDeletehttps://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9-
ReplyDelete%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
https://www.set-elbeet.com/
ReplyDeletehttps://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%BA%D8%B3%D9%8A%D9%84-
ReplyDelete%D9%85%D9%88%D9%83%D9%8A%D8%AA-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
ReplyDeletehttps://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
%D9%85%D9%86%D8%A7%D8%B2%D9%84-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D8%AE%D8%B2%D9%8A%D9%86-
ReplyDelete%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
ReplyDelete%D9%85%D8%AC%D8%A7%D9%84%D8%B3-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D8%B3%D9%84%D9%8A%D9%83-
ReplyDelete%D9%85%D8%AC%D8%A7%D8%B1%D9%8A-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%B9%D8%B2%D9%84-
ReplyDelete%D8%A7%D8%B3%D8%B7%D8%AD-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D8%B3%D9%84%D9%8A%D9%83-
ReplyDelete%D9%85%D8%AC%D8%A7%D8%B1%D9%8A-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%B1%D8%B4-
ReplyDelete%D9%85%D8%A8%D9%8A%D8%AF%D8%A7%D8%AA-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
ReplyDelete%D9%85%D8%B3%D8%A7%D8%A8%D8%AD-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
ReplyDelete%D9%85%D9%83%D9%8A%D9%81%D8%A7%D8%AA-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
ReplyDelete%D8%B4%D9%82%D9%82-%D8%A8%D8%A7%D9%84%D8%AF%D9%85%D8%A7%D9%85/
ReplyDeleteشركة نظافة بالرياض
شركة مكافحة دفان بالرياض
شركة رش مبيدات بالدمام
شركة نقل عفش من الرياض إلي الإمارات
شركة كشف تسربات المياه بالرياض
شركة نقل اثاث بالرياض
شركة تنظيف خزانات المياه بالرياض
شركة نقل وتخزين اثاث بالرياض
ReplyDeleteشركة مكافحة دفان بالرياض
ReplyDeletehttps://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
%D9%85%D8%AC%D8%A7%D9%84%D8%B3-%D8%A8%D8%A7%D9%84%D8%AE%D8%B1%D8%AC/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D9%85%D9%83%D8%A7%D9%81%D8%AD%D8%A9-
%D8%AD%D8%B4%D8%B1%D8%A7%D8%AA-%D8%A8%D8%A7%D9%84%D8%AE%D8%B1%D8%AC/
https://www.set-elbeet.com/%D8%B4%D8%B1%D9%83%D8%A9-%D8%AA%D9%86%D8%B8%D9%8A%D9%81-
%D9%85%D9%86%D8%A7%D8%B2%D9%84-%D8%A8%D8%A7%D9%84%D8%AE%D8%B1%D8%AC/
ReplyDeleteشركة شحن اثاث من الرياض الى الامارات
شركة شحن اثاث من السعودية الى الامارات
شركة تنظيف خزانات بالخرج
ReplyDelete
ReplyDeleteشركة قص اشجار بالرياض
كافة أنواع كبائن السيرفر والشبكات
ReplyDeleteشركة تركيب ستائر بالرياض
شركة قص اشجار بالرياض
فني تركيب ستلايت بالرياض
شركة عزل فوم بالرياض
ReplyDeleteشركة مكافحة حشرات ببيشة
شركة تنظيف مجالس وكنب ببيشة
فني تركيب ستلايت بالرياض
نجار بالرياض
فني بالرياض
فني تركيب دش بالدمام
فني تركيب ستلايت بالرياض
شركة ترميم بالرياض
شركة ترميم بالخرج
ReplyDeleteشركة تنظيف بالرياض
شركة تركيب اثاث ايكيا بالرياض
.
.
فني
شركة تركيب اثاث ايكيا بالرياض
فني ستائر بالرياض
شركة تركيب اثاث ايكيا بالرياض
شركة تركيب ستائر بالرياض
شركة نقل اثاث من الرياض الى الامارات
فني تركيب ستلايت بالرياض
ReplyDeleteفني تركيب دش بالرياض
Casinos and gambling: An unbiased look at the gaming industry's
ReplyDeleteA 속초 출장샵 look at the 여수 출장샵 casino industry's 천안 출장마사지 most popular games: slots, video 밀양 출장안마 poker, blackjack and slot games. A 전라북도 출장마사지 look at the casino industry's most popular games:
ReplyDeleteشركة تركيب ستائر بالرياض
شركة تركيب اثاث ايكيا بالرياض
تركيب ستائر بالرياض
شركة تركيب اثاث ايكيا بالرياض