Online Rebuild of Bitmap Indexes

I was reading an old Oracle 9 performance tuning book (as you do) and it said that you could not do online rebuilds of bitmap indexes.
I did not have access to an Oracle 9 database so I tried it out in an Oracle 10 one instead and it worked:

SQL> create table tab1
  2  (col1 number)
  3  /

Table created.

SQL> create bitmap index ind1 on tab1(col1)
  2  /

Index created.

SQL> alter index ind1 rebuild online
  2  /

Index altered.

SQL>

I then remembered that we had the Oracle 11.2 version of the same book by the same author in the cupboard.
It still claimed that you could not do online rebuilds of bitmap indexes.
Remember, if you read something in a book, be sure to check it yourself.

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>