SQL> !oerr ora 8104
08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause: the index is being created or rebuild or waited for recovering
// from the online (re)build
// *Action: wait the online index build or recovery to complete
因为rebuild index online 的意外终止，导致该索引处于一种被重建的状态，经实验证明该状态下可以使用
通过以下sql定位问题的object id
SQL> select obj#,flags from ind$ where bitand(flags,512) = 512;
解决方法有两种：
其一等待smon清理
Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup. In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit ‘hit and miss’. To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.
其二调用DBMS_REPAIR.ONLINE_INDEX_CLEAN手动清理
* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix. The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not normally introduced in patchsets; therefore, this is not available in a patchset but is available in 10gR2.
- Check your patch list to verify the database is patched for Bug 3805539 using the following command and patch for the bug if it is not listed:
以下是实验步骤
参考
http://www.syksky.com/oracle/rebuild-index-online-fails-ora-8104.html
---------------------------------------------------
表的数据量情况
SQL> select count(1) from t_ind_rebuild;
COUNT(1)
----------
1650816
索引的情况
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IND_T_IND_REBUILD';
BYTES/1024/1024/1024
--------------------
.0703125
会话a
SQL> select sid from v$mystat where rownum=1;
SID
----------
193
SQL> select spid from v$process p,v$session s where s.paddr=p.addr and sid=193;
SPID
------------------------
5567
SQL> alter index IND_T_IND_REBUILD rebuild online;
alter index IND_T_IND_REBUILD rebuild online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5567
Session ID: 193 Serial number: 5
会话b
SQL> !kill -9 5567
查看rebuild online意外中断后的信息
SQL> select obj#,flags from ind$ where bitand(flags,512) = 512;
OBJ# FLAGS
---------- ----------
13308 2562
SQL> alter index ivo.IND_T_IND_REBUILD rebuild online;
alter index ivo.IND_T_IND_REBUILD rebuild online
*
ERROR at line 1:
ORA-08104: this index object 13308 is being online built or rebuilt
可以使用该索引
SQL> explain plan for select * from ivo.T_IND_REBUILD where object_id<10;
Explained.
SQL> set lines 180
SQL> set pages 10000
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1611265204
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 712 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_REBUILD | 8 | 712 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_IND_REBUILD | 8 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10)
14 rows selected.
SQL> declare
2 isclean boolean;
3 begin
4 isclean := false;
5 while isclean = false loop
6 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(13308, dbms_repair.lock_wait);
7 dbms_lock.sleep(10);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL>
SQL> select obj#,flags from ind$ where bitand(flags,512) = 512;
no rows selected
SQL>
SQL>
SQL>
SQL> alter index ivo.IND_T_IND_REBUILD rebuild online;