• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 26051
  • Last Modified:

ORA-08103 object no longer exists?!!

This is a rather fun error. I have done some research and this is what I know so far:

1) The way to get this error is to start a 'long running' query and then have another user (or application) delete one (or more) of the objects that your query requires.

I'm confused because I can verify that no other user has modified the database AND that no other process has modified the database.

I have seen write errors on one of the scsi disks that some of the database files are on.

I have been able to successfully shut down the database and restart it.

Also I can run a query against one of the 'nonexistant' tables and it always fails at the same point.

I don't have access to the physical machine (this is part of my problem).

Assuming I can get the drive(s) in question to work properly (or replace them) what, if any, recourse do I have about objects that the database says no longer exist?

Not that it's a surprise but I don't have backups of this information (yet).

Any help is appreciated.
1 Solution

When you say that you can verigy that your database is not been modified by any user or process , do you have audit feature on.  Or by other mean you are confirming it.

Secondly, How can you run a query againist a non exixting table  big mistry,  help us out. What error at which same point, U do not have access to the machine, where U have seen the SCSI disk error.

Do you have triggers in any process to delete system objects.

After a Drop or Truncate there is no OPSSS, they are gone.

If you do not have backup , do you mean any backup or current backup. Do U have Archive logs.

If yes, than Just go with Media recovery process. and do an incomplete recovery till the time the objects were there.

Good luck.

Seems this is a bug, a very longliving bug from Oracle 7 up to Oracle 9i Release 2:

Bug: 424581 Core Dump or ORA 8103 Errors from various SQL statements ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Versions Affected ~~~~~~~~~~~~~~~~~     This problem only affects Oracle Server release on certain     platforms.  Ports Affected ~~~~~~~~~~~~~~     Port                        Oracle Bug Reference     ~~~~                        ~~~~~~~~~~~~~~~~~~~~     Alpha OSF / Digital Unix    -               **1     DG Aviion 88K               Bug:425100     DG Aviion Intel             Bug:425102     HPUX                        Bug:437448      **2     IBM RS/6000 AIX          Bug:436670     NCR 3000 Unix (Svr4)        Bug:425217     NEC UX/4800 UP/EWS 4800     Bug:425912     Sequent PTX                 Bug:425040     Sun Solaris 5.x             Bug:425483     Sun Solaris Intel           Bug:425519     UnixWare                    Bug:425981      All other ports have the relevant bug fixed in the patch release.      **1: The patch for Bug:424581 is included in the patch release          *BUT*  Digital Unix "4.0" requires the patch for [BUG:424307]      **2: Note that the HPUX patch release notes state that this bug is          fixed in the patch. This is incorrect - you need to apply fix 437448          after installing  Description ~~~~~~~~~~~     Certain SQL statements may fail with either an Oracle Error ORA 8103     or a core dump in the function KCBGTCR.  More specifically, the problem is     most likely to occur in SQL statements using AND-EQUAL access paths.   Likelihood of Occurrence ~~~~~~~~~~~~~~~~~~~~~~~~     It is highly likely this problem will be encountered on any of the     Oracle ports listed above on, especially if AND-EQUAL access     paths are used.   Possible Symptoms ~~~~~~~~~~~~~~~~~     Symptoms include:          ORA 7445 with 'kcbgtcr' in the argument list or stack trace.         ORA 8103 from AND-EQUAL type queries.         Incorrect query results from AND-EQUAL type queries.   Workaround / Patches ~~~~~~~~~~~~~~~~~~~~     For some queries you may be able to disable AND-EQUAL steps in the     execution plan. It is recommended that a patch be applied in all cases.     You should first make sure is fully installed then apply the     relevant patch.      Contact Oracle Worldwide Support regarding availability of patches for     the platforms listed above.

Problem Description:  ====================    When you issue a select statement such as:       select * from tableA where x='something' and y='another'    and you recieve one of the following errors:       Core Dump        - OR -       ORA-08103:  "object no longer exists"     // *Cause:  the object has been deleted by another user since                  the operation began.     // *Action: Remove references to the object.    To correct this problem, you have been told by a third-party vendor  to upgrade from RDBMS version to version because   of a problem with the "where equals" clause and an "and equals"   clause.      Problem Explanation:  ====================  This is Base Bug 424581.  Applying the patch for this bug on a unix platform will not upgrade the Oracle version to as this version does not exist. The Oracle version will remain at  On VMS does exist.  
asmoAuthor Commented:

I will look into the archive logs, perhaps I can do a point
in time recovery.

I assume it was not another user (as the only account on
the db is my own). It is possible that someone cracked
my password, but unlikely. I do have error messages on
the machine from one of the hard drives.

There are no triggers.

I have no backup of the physical devices. I may have
archive logs, I will be checking that later today.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

asmoAuthor Commented:

I still think (based on the new information about the drive
having problems) that this is a media error and that the
table is striped across multiple drives. (thus only part
of the table is affected?)

I am not using an AND EQUALS in my query, it is just a
SELECT * FROM query.

On a side note I have a (nearly) identical database setup
that has not had this problem (ever).

Before you do any recovery ,  do a COLD backup of the current system and then perform a Media recovery. And a Cold backup after the Recovery.

For future, just backup your system daily, if nothing else a full/owner export is the minimum U should do daily for the schema/s which are in use , according to your business requirement.

For one of these errors.

Good Luck.

asmoAuthor Commented:

I will perform a cold backup before I attempt a recovery.

I will also perform a cold backup after the recovery

This system is a preproduction beta system. It was scheduled
to have tape backup on it as well as to have a live

But none of my future planning helps the current situation.

I will not be able to do anything more on this until tommorow when I have a set of hands that I can direct.
asmoAuthor Commented:

Just a quick explaination for the accepted answer:

1) The drive did have a failure in it, this was a media failure that not only
took out a single table but also the archivelogs (thus no recovery is possible).

2) The information given, while not able to solve this problem in general did
give me an important lead when I do finally get this system back up and
running again, allowing me to have a better policy in place for the future.

Not sure if I needed to justify this one or not, but wanted too.

Also sorry for the huge delay in getting back to this, sometimes I am not
able to get online, and when I do I don't always check in here.

Thanks  Asmo ,   for accepting my reply,  I am happy  I can be of some help to you.   Good luck.

Hi All,

I got the similar issue of "object doesnt exist" while running oracle sql in DataStage. Error is raised on today datastage job run, when the same ran successfully on yesterday.

Your suggestions will be appreciated...!!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now