Index 'xxx01.Z00_ID' ... is in unusable state

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Product: Aleph
Product Version: 20, 21, 22, 23
Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

Description:
You get the message: ORA-01502: index 'xxx01.Z00_ID' or partition of such index is in unusable state. When you try to rebuild the z00_id (with UTIL-A-17-3), you get the message: ORA-00001: unique constraint (xxxnn.Znn_ID) violated.

Resolution:
This problem can occur when duplicate doc records have been loaded with DOC_SEQ (or DOC_ONLY) specified. To delete these duplicates we did the following. Note that this deletes *both* of the records which contain the duplicate key. (You should consult Ex Libris Support before doing this. )
1. SQL> select z00_doc_number from z00 group by z00_doc_number having count(*) > 1;
2. UTIL-F-12 to verify that the doc records shown in step 1 did not have links.
3. UTIL-A-17-4 to drop the z00_id. (If you don't do this, the following delete will not work.)
4. SQL> delete from z00 where z00_doc_number > xxxxxxxxx and z00_doc_number < yyyyyyyyy;
5. commit;
6. then UTIL-A-17-2 for z00_id to build the index.

Note: The above pertains to the z00_id only. If you have a problem with Oracle indexes on ALEPH index tables (z01, z02, z95, etc.), then please consult the article "Cannot CREATE UNIQUE INDEX; duplicate keys found; using util a/17/18" (KB 8192-424) instead.

Article last edited: 12-Mar-2016