測試index online rebuild故障記錄

測試資料

SQL> select object_id from dba_objects where object_name = 'ONLINE_TEST';

OBJECT_ID
----------
     75783

SQL> select FLAGS from ind$ where obj# = 75783;

     FLAGS
----------
     2

SQL> select status from dba_indexes where index_name = 'ONLINE_TEST';

STATUS
--------
VALID

線上重建索引，途中殺掉程式

SQL> alter index online_test rebuild online;
alter index online_test rebuild online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2565
Session ID: 39 Serial number: 1

此時的狀態

SQL> select FLAGS from ind$ where obj# = 75783;

     FLAGS
----------
       514

SQL> select status from dba_indexes where index_name = 'ONLINE_TEST';

STATUS
--------
VALID

資料庫內殘留了一張表，用於線上重建索引時，記錄基表改變

SQL> select table_name from dba_tables where table_name like '%75783%';

TABLE_NAME
------------------------------
SYS_JOURNAL_75783

SQL> desc SYS_JOURNAL_75783;
Name                            Null?    Type
----------------------------------------- -------- ----------------------------
C0                            NOT NULL NUMBER
OPCODE                              CHAR(1)
PARTNO                              NUMBER
RID                            NOT NULL ROWID

索引仍然是可用狀態

Execution Plan
----------------------------------------------------------
Plan hash value: 688999315

---------------------------------------------------------------------------------
| Id  | Operation       | Name     | Rows     | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |    13 |          |          |
|*  2 |   INDEX RANGE SCAN| ONLINE_TEST |     8 |   104 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

但是無法對索引進行操作了

SQL> alter index online_test rebuild online;
alter index online_test rebuild online
*
ERROR at line 1:
ORA-08104: this index object 75783 is being online built or rebuilt


SQL> alter index online_test rebuild;
alter index online_test rebuild
*
ERROR at line 1:
ORA-08104: this index object 75783 is being online built or rebuilt

SQL> drop index online_test;
drop index online_test
           *
ERROR at line 1:
ORA-08104: this index object 75783 is being online built or rebuilt

解決方法：呼叫函式（10g及以上）

declare

     ok boolean;
begin
     ok := false;
     while ok=false loop
          ok := dbms_repair.online_index_clean(75783); --75783是索引的object_id
          dbms_lock.sleep(2);
     end loop;
end;

/

dbms_repair.online_index_clean如果不加任何引數，就是清理所有online rebuild中斷的索引

PL/SQL procedure successfully completed.

SQL> select FLAGS from ind$ where obj# = 75783;

     FLAGS
----------
     2

SQL> select table_name from dba_tables where table_name like '%75783%';

no rows selected

SQL> alter index online_test rebuild online;

Index altered.