My colleague was trying rebuild index and he added script assuming that all indexes can be rebuilt online. But ended up with errors as his database contains all sorts of indexes – Normal , function based, Domain, LOB and IOT index.
Here is the list of index types and which can be performed online.
Normal and Function Based index
These can be rebuilt online without any problem
alter <index_name> rebuild online;
Domain Index
These can be rebuilt online however can’t specify “no logging”
LOB indexes
These needs to be Moved.
alter table <your_table> move lob (<lob_column>) store as (tablespace <tablespace>);
Index-Organized Tables (IOT)
Can’t be rebuilt Online, instead they needs to be moved online.
Per documentation…Because index-organized tables are primarily stored in a B-tree index, can encounter fragmentation as a consequence of incremental updates. MOVE statement reduces the fragmentation.
ALTER TABLE <IOT_name> MOVE ONLINE;
Other Restrictions Using ONLINE clause:
- Parallel DML is not supported during online index building. If you specify ONLINE and subsequently issue parallel DML statements, then Oracle Database returns an error.
- You cannot specify ONLINE for a bitmap join index or a cluster index.
- For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.
Oracle Enablement 