Adityanath's Database & Cloud Blog

ORA-08103 Object no longer exists during select operation

Published on

41,325 hits

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

44 responses to “ORA-08103 Object no longer exists during select operation”

  1. Johng794 Avatar
    Johng794

    Great, thanks for sharing this article. Really Cool.

    Reply
    1. Adityanath Dewoolkar Avatar
      Adityanath Dewoolkar

      Hello There,

      Thanks for visiting and appreciate your feedback.:-)

      Regards,
      Adi

      Reply
  2. XyleeA Avatar
    XyleeA

    Hi,

    Thanks for this detailed explanation. But do you have any suggestions on how to workaround this error?

    Reply
    1. Adityanath Dewoolkar Avatar
      Adityanath Dewoolkar

      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

      Reply
      1. Vijay Pratap Singh Avatar
        Vijay Pratap Singh

        any proposed solution appreciated please to avoid with this concurrent situation.

      2. Adityanath Dewoolkar Avatar
        Adityanath Dewoolkar

        Best solution is to inform application team not to run jobs which has cross references on same objects concurrently.

        Regards,
        Adi

  3. mohammad ibrahim balouch Avatar
    mohammad ibrahim balouch

    Dear friend,
    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.

    Reply
    1. Adityanath Dewoolkar Avatar
      Adityanath Dewoolkar

      Hello There,

      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!!!

      Regards,
      Adi

      Reply
  4. Frederica Lamattina Avatar
    Frederica Lamattina

    Respect to website author, some good selective information.

    Reply
  5. Alphonse Desai Avatar
    Alphonse Desai

    Some really nice stuff on this site, I like it.

    Reply
  6. Joie Oehm Avatar
    Joie Oehm

    glad to be one of several visitants on this awe inspiring internet site : D.

    Reply
  7. Kindra Kober Avatar
    Kindra Kober

    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!

    Reply
  8. waive Avatar
    waive

    Great infoｒmation. Lսcky me I Ԁiscovereɗ your wеbsite
    by chance (stumbleupon). I’vе saved it for later!

    Reply
  9. Avatar
    Anonymous

    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.

    Reply
  10. Jacquelyne Montgonery Avatar
    Jacquelyne Montgonery

    some truly prize articles on this internet site, saved to favorites.

    Reply
  11. Grover Rances Avatar
    Grover Rances

    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!!

    Reply
  12. Gregg Furkin Avatar
    Gregg Furkin

    It’s difficult to find knowledgeable people in this particular topic, but you seem like you know what you’re talking about! Thanks

    Reply
  13. Ashton Avatar
    Ashton

    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..

    Reply
  14. Avatar
    Anonymous

    Pretty! This has been a really wonderful article.

    Many thanks for supplying these details.

    Reply
  15. Henriette Lewman Avatar
    Henriette Lewman

    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.

    Reply
  16. ปั้มไลค์ Avatar
    ปั้มไลค์

    Like!! Great article post.Really thank you! Really Cool.

    Reply
  17. g Avatar
    g

    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!

    Reply
  18. Jule Care Avatar
    Jule Care

    Just what I was looking for, regards for putting up.

    Reply
  19. สลอต Avatar
    สลอต

    Hi to every one, it’s truly a nice for me to pay a
    quick visit this web page, it includrs valuable Information.

    Reply
  20. Andreas Newsham Avatar
    Andreas Newsham

    Everything is very open with a very clear explanation of the issues. It was truly informative. Your site is useful. Thank you for sharing!

    Reply
  21. Steven Candland Avatar
    Steven Candland

    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!

    Reply
  22. Adan Morosco Avatar
    Adan Morosco

    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.

    Reply
  23. Jane Roman Avatar
    Jane Roman

    Glad to be one of many visitants on this amazing internet site : D.

    Reply
  24. Demetrice Longenberger Avatar
    Demetrice Longenberger

    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.

    Reply
  25. Homer Avatar
    Homer

    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.

    Reply
  26. Gladys Avatar
    Gladys

    I am regular reader, how are you everybody? This piece of writing posted at
    this web page is actually pleasant.

    Reply
  27. Dipak Chandnani Avatar
    Dipak Chandnani

    Hello Adi,

    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.

    Reply
    1. Adityanath Dewoolkar Avatar
      Adityanath Dewoolkar

      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:

      https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:23417970272903

      Regards,
      Adi

      Reply
  28. Londa Hoopengardner Avatar
    Londa Hoopengardner

    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.

    Reply
  29. Lakeesha Wilm Avatar
    Lakeesha Wilm

    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.

    Reply
  30. my ps4 games Avatar
    my ps4 games

    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.

    Reply
  31. Fred Venturi Avatar
    Fred Venturi

    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!

    Reply
  32. Wilhemina Avatar
    Wilhemina

    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!

    Reply
  33. mua bán chó poodle đáng yêu Avatar
    mua bán chó poodle đáng yêu

    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!

    Reply
  34. Kris Boak Avatar
    Kris Boak

    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!

    Reply
  35. Hayden Gockel Avatar
    Hayden Gockel

    This excellent website certainly has all the information I needed concerning this subject and didn’t know who to ask.

    Reply
  36. Roxanna Learn Avatar
    Roxanna Learn

    You must take part in a contest for among the best blogs on the web. I will suggest this web site!

    Reply
  37. Orval Lefort Avatar
    Orval Lefort

    Good day, i am doing research right now and your blog really helped me.    

    Reply
  38. Johnnyinert Avatar
    Johnnyinert

    It is remarkable, rather useful idea

    ——

    Reply

Leave a reply to Kindra Kober Cancel reply

Advertisements
Blog Stats

562,661 hits

Advertisements
Advertisements