rebuild index online prompted ora-08106

来源：互联网发布：保存视频的软件编辑：程序博客网时间：2024/05/29 12:24

　在生产库rebuild online时cancel后，重新build后提示：ORA-08106: cannot create journal table TEST.SYS_JOURNAL_68422,查询相关文档后，可以用dbms_repair.online_index_clean方式修复。　　　　

　　为了保险起见，找了一个LINUX 11.2.0.3 RAC环境测试，脚本如下：

create table temp

(owner varchar2(20),
object_name varchar2(50),
SUBOBJECT_NAME varchar2(20),
OBJECT_TYPE varchar2(20) ,
status varchar2(20),
create_time timestamp
)
partition by range(create_time)
(
partition PAR_2013_12_24 values less than (TIMESTAMP' 2013-12-25 00:00:00'),
partition PAR_2013_12_25 values less than (TIMESTAMP' 2013-12-26 00:00:00'),
partition PAR_2013_12_26 values less than (TIMESTAMP' 2013-12-27 00:00:00'),
partition PAR_2013_12_27 values less than (TIMESTAMP' 2013-12-28 00:00:00')，
partition MAX_PART values less than (maxvalue)
);

create index t_idx_temp on temp(owner,object_name,OBJECT_TYPE) local;

重复插入，直到几十万数据：
insert into temp
select a.owner, a.OBJECT_NAME, substr(a.SUBOBJECT_NAME,1,20), a.OBJECT_TYPE, a.status, sysdate　
From dba_objects a;

另开一个SQLPLUS插入一批数据不提交，接着REBUILD索引，等待10几20秒后Cancel，再REBUILD后出现错误：

SQL> set time on
10:01:52 SQL> SET SERVEROUTPUT ON

10:07:29 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;

alter index t_idx_temp rebuild partition PAR_2013_12_25 online
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

10:11:30 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;
alter index t_idx_temp rebuild partition PAR_2013_12_25 online
*
ERROR at line 1:
ORA-08106: cannot create journal table TEST.SYS_JOURNAL_68422

切换到SYS用户
10:17:25 SQL> conn / as sysdba
Connected.
10:17:38 SQL> DECLARE
10:17:45 2 isClean BOOLEAN;
10:17:45 3 BEGIN
isClean := FALSE;
WHILE isClean=FALSE
LOOP
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID, DBMS_REPAIR.LOCK_WAIT);
DBMS_LOCK.SLEEP(10);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/10:17:45 4 10:17:45 5 10:17:45 6 10:17:45 7 10:17:45 8 10:17:45 9 10:17:45 10 10:17:45 11 10:17:45 12 10:17:45 13 10:17:45 14

PL/SQL procedure successfully completed.

10:18:34 SQL> 10:18:34 SQL> conn test/test

Connected.

重新rebuild online

10:18:49 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;

Index altered.

到这为止，测试成功！

记录:生产库下使用dbms_repair.online_index_clean情况：

*******************************
10:14:43 SQL> SET SERVEROUTPUT ON
10:14:46 SQL> DECLARE
10:14:50 2 isClean BOOLEAN;
10:14:50 3 BEGIN
10:14:50 4 isClean := FALSE;
10:14:50 5 WHILE isClean=FALSE
10:14:50 6 LOOP
10:14:50 7 isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID, DBMS_REPAIR.LOCK_WAIT);
10:14:50 8 DBMS_LOCK.SLEEP(10);
10:14:50 9 END LOOP;
10:14:50 10 EXCEPTION
10:14:50 11 WHEN OTHERS THEN
10:14:50 12 RAISE;
10:14:50 13 END;
10:14:50 14 /

PL/SQL procedure successfully completed.

10:20:41 SQL>

耗时5分51秒

0 0