ORA-08103 Object no longer exists during select operation

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:

OERR: ORA-8103 “object no longer exists” Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)

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

Advertisements

4 thoughts on “ORA-08103 Object no longer exists during select operation

    • Hello There,

      Thanks for visiting and appreciate your feedback.:-)

      To answer your question:

      Anything that alters an underlying object that is being queried can create this issue. Examples:

      – truncate table
      – alter table move
      – drop table

      You need to identify simultaneous operations on that object, then you can check with your application team to avoid concurrent operations on object.

      Hope this helps!!!!

      Regards,
      Adi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s