Tuesday, April 09, 2013

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>

Jonathan Lewis wrote the book advertised below.

If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

No comments: