索引重建失败 清理online_index_clean

在本地的测试库中，本来空间就不足，结果创建了一个表有600多万条记录，想创建一个index. 物理段有340多M.

临时段大小有100M,结果想创建一个索引，总是报临时表空间不足的错误。

[ora11g@rac1 test]$ ksh test.sh "create unique index t_pk on t(object_id) tablespace pool_data nologging online;" 
create unique index t_pk on t(object_id) tablespace pool_data nologging online 
                            * 
ERROR at line 1: 
ORA-01652: unable to extend temp segment by 128 in tablespace TEMPTS1

排除了索引所在的表空间不足的问题，实时监控了一下，发现确实临时表空间使用率在瞬间飙到100%，然后就报了ORA-01652的错误。

在尝试各种方法之后，先扩大临时段再次尝试。

alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200M；

结果再次尝试的时候，就有了如下的错误。
create unique index t_pk on t(object_id) tablespace pool_data nologging online
*

ERROR at line 1: 
ORA-00603: ORACLE server session terminated by fatal error 
ORA-01114: IO error writing block to file  (block # ) 
ORA-01114: IO error writing block to file 201 (block # 15439) 
ORA-27072: File I/O error 
Additional information: 4 
Additional information: 15439 
Additional information: 4096 
Process ID: 5683 
Session ID: 18 Serial number: 103

Elapsed: 00:00:21.11 
ERROR: 
ORA-03114: not connected to ORACLE

一看就有些崩溃了，以为数据库又挂了。一看进程，还在，还能连接。

[ora11g@rac1 dbm_lite]$ ps -ef|grep smon 
ora11g    2357    1  0 05:32 ?        00:00:01 ora_smon_TEST01 
ora11g    5746  5327  0 06:27 pts/0    00:00:00 grep smon

查看alert日志。

Fri Jun 06 06:26:14 2014 
alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200M 
Completed: alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 200M 
Fri Jun 06 06:26:39 2014 
online index (re)build cleanup: objn=15331 maxretry=2000 forever=0 
Fri Jun 06 06:26:57 2014 
Non critical error OR

那就再次尝试创建，结果错误接二连三。

再次创建，提示索引已经存在了。  
create unique index t_pk on t(object_id) tablespace pool_data nologging 
                    * 
ERROR at line 1: 
ORA-00955: name is already used by an existing object

那我删除重建呢。

drop index t_pk 
          * 
ERROR at line 1: 
ORA-08104: this index object 15334 is being online built or rebuilt

试试force选项。

drop index t_pk force 
* 
ERROR at line 1: 
ORA-29862: cannot specify FORCE option for dropping non-domain index

查看Index的状态，显示是valid

********** INDEX DETAILS INFO *****************

INDEX_NAME                    TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS  NUM_ROWS LAST_ANAL G 
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- - 
T_PK                          POOL_DATA  NORMAL    UNIQUE    NO  OBJECT_ID                      TABLE      VALID                      N
 
TABLE_NAME                    INDEX_NAME                    CLUSTERING_FACTOR    BLOCKS  NUM_ROWS 
------------------------------ ------------------------------ ----------------- ---------- ---------- 
T                              T_PK                                                  39174    6856704

那我再次rebuild 可以吗？

alter index t_pk rebuild parallel 4 
* 
ERROR at line 1: 
ORA-08104: this index object 15334 is being online built or rebuilt

无奈，总不能一直等着吧。而且过了好一会儿，也不见有进展。 如果在生产环境中，那绝对算是一次事故。在早期版本中，可能只有重启，让smon来做清理了。 不知道从10g还是11g开始，有dbms_repair包，里面还包括了一个蛮实用的方法。可以解决这个问题。

SQL> declare 
  2  isClean boolean; 
  3  
  4  begin 
  5  isClean := FALSE; 
  6  while isClean=FALSE loop 
  7  isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, 
  8  dbms_repair.lock_wait); 
  9  dbms_lock.sleep(2); 
10  end loop; 
11  exception 
12  when others then 
13  RAISE; 
14  end; 
15  /

PL/SQL procedure successfully completed.

运行成功后，再次尝试。

alter index t_pk rebuild parallel 4 
* 
ERROR at line 1: 
ORA-01418: specified index does not exist

这终于是我期望的结果了，看来在生产中，index的online rebuild也要慎重使用。