ORA-08176 (SET TRANSACTION READ ONLY - Part 5)

According to Jonathan Lewis, if you do a SET TRANSACTION READ ONLY, rebuild an index then use that rebuilt index in the READ ONLY session, you get an ORA-08176. I decided to check this out in an Oracle 11.2.0.2.7 database. First I created a table with an index in the red session which follows:

 

SQL> create table tab1

  2  as select * from dba_objects

  3  /

 

Table created.

 

SQL> create index ind1 on tab1(owner)

  2  /

 

Index created.

 

SQL>

 

Then I started a READ ONLY session in blue below and used the index I had just created:

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> select count(*) from tab1

  2  where owner = 'APPQOSSYS'

  3  /

 

  COUNT(*)

----------

         5

 

SQL>

 

I went back to the red session and rebuilt the index:

 

SQL> alter index ind1 rebuild online

  2  /

 

Index altered.

 

SQL>

 

… and when I tried to use the index in the blue session again, I got an ORA-08176:

 

SQL> select count(*) from tab1

  2  where owner = 'OUTLN'

  3  /

select count(*) from tab1

                     *

ERROR at line 1:

ORA-08176: consistent read failure; rollback data not

available

 

SQL>