online rebuild index fails ora-8104

When you kill a session which is rebuilding an index this will cause an ora-8104.
SQL> ALTER INDEX I_TEST  REBUILD ONLINE ;
ALTER INDEX I_TEST  REBUILD ONLINE
*
ERROR at line 1:
ORA-08104: this index object 11040 is being online built or rebuilt

Solution:

reorganize tables online with dbms_redefinition

Sometimes it’s neccessary to change table structures or to rebuild tables.

Unfortunately you wont get a downtime to do so…. even business as usual 😉

With the package dbms_redefinition you can modify your tables online (with only a view exclusive locks)

---

short summary :

1. check for redefinition

2. create a interim table (with your new definition)

3. duplicate original table to interim table

in background a mlog-table (if rowid is used) will be updated while both tables are “in use”

4. create dependent objects (e.g. indexes, views …)

5. resync tables

6. redefinition finish

7. drop interim table

9. possible problems