ORA-08176: consistent read failure

[rohanem]

Hi,
I have a process that is simply doing a long query on a table and after a while it fails with the following error-

ORA-08176: consistent read failure; rollback data not available

Can anyone tell me whats the cause and what can be done to avoid it?

Thanks
Rohan

 

[SantaMufasa]

Rohan,

There are certain database activities that change data without rollback (consistent-read) entries. Amongst such activities are: direct loads and index creations. Since Oracle does not allow "inconsistent" results, Oracle throws an error rather than potentially inconsistent results.

The remedy for this problem is to re-run your long query, hoping that no one concurrently executes whatever it was that threw the error in the first place. There is a prevention for this error, but your updating colleagues generally consider the prevention to be an annoyance since no one can effect any changes while you are querying:

lock table s_emp in exclusive mode;

Table(s) Locked.

Then, following your activities, be certain to unlock the table with the command:

ROLLBACK;

Rollback complete.

I recommend that you do not lock the table in this fashion unless the problem is recurrent and your query is a business critical activity with higher priority that table updates.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:42 (18Aug04) UTC (aka "GMT" and "Zulu"), 13:42 (18Aug04) Mountain Time)

 

[rohanem]

Thanks Mufasa..but what if the table is partitioned on a daily basis...I am trying to query lets say mondays data and another process is loading/updating Wednesdays data..is that still going to be a problem?