测试index online rebuild故障记录

故障记录"/>

测试index online rebuild故障记录

测试数据 SQL> select count(*) from t;

  COUNT(*)
----------
    596528

SQL> create index online_test on t(object_id);

Index created.

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.

来自 “ ITPUB博客 ” ，链接：/，如需转载，请注明出处，否则将追究法律责任。

转载于:/