Yesterday, application team informed us about production job failure with error: ORA-08103 Object no longer exists. It was simple select query on object with type VIEW. I tried rerunning query which ran without any issue.
I tried to investigate, if the underlying objects have been dropped when job was running. But object creation time was in the year 2014 so definitely object was not dropped & recreated and it was present during job execution.
I tried to search in metalink support & found following note explaining issue:
According to note: We get this error when, tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.
I had a look into aud$ & found one of the underlying table in view was truncated at same time when job was executing select on view. So I got the culprit 🙂
But why oracle gave error: ORA-08103 Object no longer exists even when object was present???
If you check DBA_OBJECTS dictionary view, it has two columns OBJECT_ID & DATA_OBJECT_ID. Each object is assigned a unique number to recognise it in the database (OBJECT_ID). In the same manner, each object is linked to a segment. The data object id is assigned to that segment and any kind of change done on the physical segment would lead to give a change in that number (DATA_OBJECT_ID). Both the numbers are same initially but when the modifications happen on the segment, the DATA_OBJECT_ID changes. Both the OBJECT_ID and DATA_OBJECT_ID are the numbers used by oracle to denote the metadata for the object that is used within the data dictionary. Operations such as Truncate, Move, Rebuild Index, Spilt Partition etc would cause change in DATA_OBJECT_ID of the objects.
So this gives enough justification for oracle error: ORA-08103 Object no longer exists!!
Hope so u will find this post very useful:-)
Cheers
Regards,
Adityanath
