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:-)
Categories: Administration, ORA errors
Great, thanks for sharing this article. Really Cool.
Thanks for visiting and appreciate your feedback.:-)
Thanks for this detailed explanation. But do you have any suggestions on how to workaround this error?
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!!!!
any proposed solution appreciated please to avoid with this concurrent situation.
Best solution is to inform application team not to run jobs which has cross references on same objects concurrently.
I have the same problem with following error messages mail.
Error Message: Operation failed – Failed to execute BPM flow ‘RC-NRTRDE_OUT_DCH’: Failed to extract table ‘[Ljava.lang.Object;@384443ac’: Failed to extract records: java.sql.SQLException: ORA-08103: object no longer exists
please help to solve the issue.
We get this error when, tables are being dropped/truncated/moved 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.
So here you need to find out which sql receiving ORA-08103. You may also crosscheck last_ddl_time from dba_objects at same timestamp.
This issue mostly talks about concurrency of DDL & select operation. So avoid this concurrent operations.
Also this can happen block corruption as well.
See MOS note OERR: ORA-8103 “object no longer exists” Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1) for more details.
Hope this helps!!!
Respect to website author, some good selective information.
Some really nice stuff on this site, I like it.
glad to be one of several visitants on this awe inspiring internet site : D.
This is the best weblog for anyone who desires to find out about this topic. You notice a lot its virtually laborious to argue with you (not that I really would want aHa). You positively put a new spin on a topic thats been written about for years. Nice stuff, simply great!
Great infoｒmation. Lսcky me I Ԁiscovereɗ your wеbsite
by chance (stumbleupon). I’vе saved it for later!
It’s really a cool and useful piece of information. I am glad that you shared this useful information with us.
Please keep us up to date like this. Thank you for sharing.
some truly prize articles on this internet site, saved to favorites.
Excellent site you have got here.. It’s hard to find quality writing like yours these days. I seriously appreciate individuals like you! Take care!!
It’s difficult to find knowledgeable people in this particular topic, but you seem like you know what you’re talking about! Thanks
I’m extremely impressed with your writing skills and also with the format for your blog.
Is that this a paid topic or did you customize it yourself?
Anyway keep up the nice high quality writing, it is uncommon to see a
great weblog like this one nowadays..
Pretty! This has been a really wonderful article.
Many thanks for supplying these details.
Admiring the time and energy you put into your site and in depth information you present. It’s good to come across a blog every once in a while that isn’t the same out of date rehashed information. Great read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.
Like!! Great article post.Really thank you! Really Cool.
Hello there, just became alert to your blog
through Google, and found that it’s truly informative.
I’m gonna watch out for brussels. I will appreciate if you continue this in future.
Many people will be benefited from your writing. Cheers!
Just what I was looking for, regards for putting up.
Hi to every one, it’s truly a nice for me to pay a
quick visit this web page, it includrs valuable Information.
Everything is very open with a very clear explanation of the issues. It was truly informative. Your site is useful. Thank you for sharing!
Thanks for ones marvelous posting! I certainly enjoyed reading it, you could be a great author.I will remember to bookmark your blog and will often come back in the future. I want to encourage you to ultimately continue your great posts, have a nice morning!
It’s really a great and helpful piece of information. I’m glad that you shared this useful information with us. Please keep us up to date like this. Thanks for sharing.
Glad to be one of many visitants on this amazing internet site : D.
Usually I don’t learn post on blogs, however I wish to say that this write-up very pressured me to try and do it! Your writing taste has been surprised me. Thank you, quite great article.
I enjoy what you guys are usually up too. This sort
of clever work and reporting! Keep up the very good works guys
I’ve added you guys to blogroll.
I am regular reader, how are you everybody? This piece of writing posted at
this web page is actually pleasant.
Is there any way we can maintain the same OBJECT_ID & DATA_OBJECT_ID while truncating the table? I’m specifically looking for a way to keep the same internal IDs while the table is truncated.
I agree in the following cases it should the IDs
– Drop and recreate the table
– Data block corruption
– any other as you mentioned.
data_object_id is internally controlled by Oracle, so we do not have control on it.
You may read below nice article from ASK TOM:
Appreciating the dedication you put into your website and detailed information you offer. It’s awesome to come across a blog every once in a while that isn’t the same old rehashed information. Wonderful read! I’ve saved your site and I’m including your RSS feeds to my Google account.
I really like what you guys tend to be up too. This sort of clever work and exposure! Keep up the amazing works guys I’ve added you guys to my personal blogroll.
Heya i am for the first time here. I came
across this board and I in finding It truly helpful & it helped me out much.
I am hoping to give one thing again and aid others such as you aided me.
Howdy! I’m at work surfing around your blog from my new iphone! Just wanted to say I love reading through your blog and look forward to all your posts! Keep up the superb work!
It’s a shame you don’t have a donate button! I’d definitely donate to this fantastic blog!
I guess for now i’ll settle for book-marking and adding your
RSS feed to my Google account. I look forward to new updates and will
share this website with my Facebook group. Talk soon!
Hello there, just became aware of your blog through Google, and found that it is truly informative. I’m gonna watch out for brussels. I will be grateful if you continue this in future. A lot of people will be benefited from your writing. Cheers!
Hello! I’m at work browsing your blog from my new iphone 3gs! Just wanted to say I love reading through your blog and look forward to all your posts! Keep up the superb work!
This excellent website certainly has all the information I needed concerning this subject and didn’t know who to ask.
You must take part in a contest for among the best blogs on the web. I will suggest this web site!
Good day, i am doing research right now and your blog really helped me.