ITPub博客

首页 > 数据库 > Oracle > 测试index online rebuild故障记录

测试index online rebuild故障记录

原创 Oracle 作者:liiinuuux 时间:2014-03-27 14:39:59 0 删除 编辑
测试数据
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博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1130714/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 体验mutex
下一篇: undo损坏测试
请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    313119