ORA-08103: object no longer exists tips Oracle Error Tips by Stephanie F.

Question:  I am getting the ORA-08103 error when trying to create an index.

 

How do I fix the ORA-08103 error?

Answer:  The Oracle docs note this on the ora-08103 error:

ORA-08103 object no longer exists
 
Cause: The object has been deleted by another user since the operation began.  Or a prior incomplete recovery restored the database to a point in time during the deletion of the object  

Action: Remove references to the object or delete the object if this is the result of an incomplete recovery.

Internet sources show that there are many bugs associated with ORA-08103.  A description of the error per the documents is as follows:

This error can occur, if the header block has an invalid block type or data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header. This error can be treated as a block corruption.

There are several likely reasons for this error, and you should always check your alert log for details.  This error often requires opening a service request with MOSC:

0:  A software bug (see list below).

1:  You are not signed-on as the table owner.

2:  Database corruption of a header block.

3:  Accidental delete of the target table. (check the recyclebin)

4: Data file I/O error (check alert log)

5: corruption in UNDO log (drop and re-create)

6. An index is disabled or is offline.

Possible solution include:

1:  Set db_file_multiblock_read_count to 1.
 
2:  Run dbv on all data files. 

3:  Run catalog.sql, catproc.sql and utlrp.sql to ensure no data dictionary corruption.
 
4:  Purge recyclebin & dba_recyclebin.

5: When the ORA-08103 is on an index operation, place the index online or make index usable

select index_name , status from user_indexes;
 
INDEX_NAME                     STATUS
------------------------------ --------
IDX_CUST                       UNUSABLE

alter index idx_cust rebuild online;

Bugs and ORA-08103

Below are the twelve bugs which contain ORA-08103 in Oracle Enterprise Edition; version 11g

• Bug 13618170 ORA-8103 for create index online
  
• Bug 5523799
  Abstract: Various OERI (eg kcbgtcr_12) using ASSM managed segments
  Versions affected: 9.2.0.8 & 10.1.0.5
  Fixed in version: 10.1.0.6
  Symptoms:
  Operations such as TRUNCATE on ASSM segments can lead to subsequent ORA-600 kcb* errors such as ORA-600 [kcbnew_3], ORA-600 [kcbgtcr_3], ORA-600 [kcbgtcr_12]
  Details:
  This problem is introduced in the 9.2.0.8 and 10.1.0.5 patch sets by the fix for bug 3279497
  Operations such as TRUNCATE on ASSM segments can lead to subsequent ORA-600 kcb* errors such as ORA-600 [kcbnew_3], ORA-600 [kcbgtcr_3], ORA-600 [kcbgtcr_12] .
  Workaround:
  Avoid TRUNCATE operations on ASSM managed segments, or use TRUNCATE with a KEEP STORAGE option. Once the issue has occurred then the problem is that there is a CURRENT block image in the cache which should not be there. In 10g flush the buffer cache to remove the problem block image/s from the cache. (In 9.2 there is no SQL to flush the buffer cache)
  Patch details:
  One-off patch available for few platforms on top of 9.2.0.8 & 10.1.0.5
  Check the MOSC for Patch 5523799 availability.

 

• Bug 5637976
  Abstract: ORA-8103 EVEN WITH THE WORKAROUND FROM Bug 3569503
  Versions affected: 10.2.0.2
  Fixed in version: 11.1
  Symptoms:
  1) Concurrent inserts and exports on ASSM tables
  2) ORA-8103/ORA-1410
  3) Redo dump shows 'ktspbfredo - Format Pagetable Datablock' for that rdba
  Details:
  Concurrent inserts and exports on ASSM tables can lead ORA-08103 error
  Workaround:
  None
  Patch details:
  One-off patch available for few platforms on top of 10.2.0.3
  Check the MOSC for Patch 5637976 availability.