手动清理被中断的索引

              手动清理被中断的索引

     以前索引在重建时候异常中断的时候，处理很麻烦。到10.2后的版本中
出现了dbms_repair.online_index_clean这个过程来清理索引的状态，而实
际上这个过程是重置一个标志位来达到这样的目标.

来创建一个表
SQL>create table t1 pctfree 60 as select * from dba_objects

declare
i int;
begin
    for i in 1..100
       loop
           insert into t1 select * from dba_objects;
       end loop;
    commit;
    end;

创建索引
SQL> create index t1_object_id on t1(object_id) pctfree 50;
 
Index created
 

实际上索引的状态是存放在ind$表中的flags中.
SQL> select a.obj#, a.flags
  2  from ind$ a,dba_objects b
  3  where obj#=b.object_id
  4  and object_name='T1_OBJECT_ID'
  5  and object_type='INDEX'
  6  and owner='SYS'
  7  ;
 
      OBJ#      FLAGS
---------- ----------
    107662       2050
 

 flags的定义如下

flags         number not null,     
                /* mutable flags: anything permanent should go into property */
                                                    /* unusable (dls) : 0x01 */
                                                    /* analyzed       : 0x02 */
                                                    /* no logging     : 0x04 */
                                    /* index is currently being built : 0x08 */
                                     /* index creation was incomplete : 0x10 */
                                           /* key compression enabled : 0x20 */
                                              /* user-specified stats : 0x40 */
                                            /* secondary index on IOT : 0x80 */
                                      /* index is being online built : 0x100 */
                                    /* index is being online rebuilt : 0x200 */
                                                /* index is disabled : 0x400 */
                                                     /* global stats : 0x800 */
                                            /* fake index(internal) : 0x1000 */
                                       /* index on UROWID column(s) : 0x2000 */
                                            /* index with large key : 0x4000 */
                             /* move partitioned rows in base table : 0x8000 */
                                 /* index usage monitoring enabled : 0x10000 */
                      /* 4 bits reserved for bitmap index version : 0x1E0000 */
                                            /* index is invisible : 0x200000 */
                                       /* Delayed Segment Creation: 0x400000 */
                                              /* index is partial : 0x800000 */
                                                   /* 2 free bits: 0x3000000 */
                                      /* Delayed Segment Creation: 0x4000000 */
                                    /* online index cleanup phase: 0x8000000 */
                                   /* index has orphaned entries: 0x10000000 */
                                 /* index is going to be dropped: 0x20000000 */
                                 /* oltp high index compression : 0x40000000 */
                                   /* oltp low index compression: 0x80000000 */

新创建的索引状态是0x802.

SQL> alter index T1_OBJECT_ID rebuild online;
 

来看看索引重建状态的时候标志位是多少

SQL> select a.obj#, a.flags
  2  from ind$ a,dba_objects b
  3  where obj#=b.object_id
  4  and object_name='T1_OBJECT_ID'
  5  and object_type='INDEX'
  6  and owner='SYS'
  7  ;
 
      OBJ#      FLAGS
---------- ----------
    107662       2562   --0xA02

可以看当索引在重建的时候标志位0x200被设置

如果索引在重建的时候，异常中断，那么接下来重建就会报错.

SQL> alter index T1_OBJECT_ID rebuild online;
 
alter index T1_OBJECT_ID rebuild online
 
ORA-03113: end-of-file on communication channel
Process ID: 19770
Session ID: 18 Serial number: 29560
 
SQL> alter index T1_OBJECT_ID rebuild online;
 
alter index T1_OBJECT_ID rebuild online
 
ORA-08104: this index object 107662 is being online built or rebuilt
 
SQL> alter index T1_OBJECT_ID rebuild;
 
alter index T1_OBJECT_ID rebuild
 
ORA-08104: this index object 107662 is being online built or rebuilt

两种方法重建报错，看我们可以设置标志位为1024(0x400）来骗过oracle

SQL> update ind$ set flags=1024
  2  where obj#=107662
  3  ;
 
1 row updated
 
SQL> commit;

SQL> drop index t1_object_id;
 
drop index t1_object_id
 
ORA-08104: this index object 107662 is being online built or rebuilt

手动再drop index,虽然drop不成功，不过产生了递归调用，所以reubild竟然成功了.
 
SQL> alter index T1_OBJECT_ID rebuild;
 
Index altered
 

SQL> alter index T1_OBJECT_ID rebuild online;
 
alter index T1_OBJECT_ID rebuild online
 
ORA-08106: cannot create journal table SYS.SYS_JOURNAL_107662

再次试图rebuild online还是失败,问题就来了，这个内部表已经存在了，手动删除这个表.

SQL> drop table SYS.SYS_JOURNAL_107662;
 
Table dropped
 
SQL> alter index T1_OBJECT_ID rebuild online;
 
Index altered
 
再次rebuild online成功.

 

SQL> set autotrace on;
SQL> select object_name from t1 where object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1922066283

--------------------------------------------------------------------------------
--------------------

| Id  | Operation                           | Name         | Rows  | Bytes | Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT                    |              |     1 |    79 |
 2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |     1 |    79 |
 2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | T1_OBJECT_ID |     1 |       |
 2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------

测试一把，这个索引已经能被正常使用了，修改成功.