Online Index Rebuild fails with ORA-08106: cannot create journal table

Before investigation few things to remember about online index rebuild

It will do a full table scan on the base table.

A journal table is maintained for DML data, which has changed during this index rebuilding operation.

At the end of index rebuild, it will merge the journal table with the base table. At this time, it has to release the exclusive lock on the base table to complete the activity.

Tables named SYS_JOURNAL_% are temporary tables and their only use is as working tables for an online index rebuild command

— Check which JOURNAL tables are created SQL> column OBJECT_NAME format a30 SQL> select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where object_name like 'SYS_JOURNAL%'; OWNER OBJECT_NAME OBJECT_ID ——- ———————- ———- SCOTT SYS_JOURNAL_11282 63502— Find out mapping for INDEX & JOURNAL SQL> column TEMP_TABLE_NAME format a30 SQL> select a.object_name, b.table_name temp_table_name 2 from dba_objects a, 3 ( select substr(object_name,13) as obj_id, 4 object_name as table_name 5 from dba_objects 6 where object_name like 'SYS_JOURNAL_%') b 7 where a.OBJECT_ID = b.obj_id; OBJECT_NAME TEMP_TABLE_NAME ———— —————– DAILY_JOB_IX1 SYS_JOURNAL_11282— SMON process is responsible for cleaning up the JOURNAL tables. But sometimes SMON cannot perform its and task. We can force it by using following PL/SQL (This is provided by Oracle) SQL> declare isclean boolean; 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; end; /PL/SQL procedure successfully completed.

But in my case this did not help i.e. it did not clean up the JOURNAL entries. Also bouncing instance did not help.

— Alert log has evidence that, it tried cleaning it online index (re)build cleanup: objn=11282 maxretry=2000 forever=0 online index (re)build cleanup: objn=11618 maxretry=2000 forever=0— Now only option is to drop these JOURNAL tables manually

But there is another problem. JOURNAL tables are considered as data dictionary tables. So we need to follow steps provided my oracle for data dictionary maintenance (I strongly recommend to involve oracle support at this point) SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.— This is most important for data dictionary maintenance SQL> startup restrict ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2211064 bytes Variable Size 1979712264 bytes Database Buffers 1207959552 bytes Redo Buffers 16953344 bytes Database mounted. Database opened.

SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects where object_name like 'SYS_JOURNAL%'; OWNER OBJECT_NAME OBJECT_ID —– —————– ———- SCOTT SYS_JOURNAL_11282 63502

SQL> drop table SCOTT.SYS_JOURNAL_11282 purge; Table dropped.

SQL> select OWNER, OBJECT_NAME, OBJECT_ID from dba_objects where object_name like 'SYS_JOURNAL%'; no rows selected SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2211064 bytes Variable Size 1979712264 bytes Database Buffers 1207959552 bytes Redo Buffers 16953344 bytes Database mounted. Database opened.— JOURNAL tables are dropped now. But online index rebuild now failing with different error now SQL> alter index SCOTT.DAILY_JOB_IX1 rebuild online; alter index SCOTT.DAILY_JOB_IX1 rebuild online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (SYS.I_INDREBUILD1) violated— Later I had to use following to clean up the entries in SYS tables and then rebuild online worked SQL> declare isclean boolean; 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; end; / PL/SQL procedure successfully completed.

SQL> alter index SCOTT.DAILY_JOB_IX1 rebuild online; Index altered.SMON would have eventually cleaned up these entries. But cleanup cycle is executed every 60 mins & there is a possibility that SMON cannot get a lock on the object with NOWAIT it will just try again later.

Metalink Ref docs –

_NOTE:1324941.1 – How To Find Out Temporary Table Sys_journal_xxxxx Is Created By Rebuilding Which Index_

_NOTE:1378173.1 – How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ?_

_NOTE:247487.1 – Rebuilding Index Online Gives ORA-8106_

Advertisements### Rate this:

One blogger likes this.### _Related_ Alter table move lob results in unusable indexesIn "Oracle Bugs"

Convert non-partition table to partition table using Oracle redefinitionIn "Oracle Availability"

Enforcing row level security using Virtual Private Database (VPD)In "Oracle Security" Oracle Index Online Index Rebuild fails with ORA-08106: cannot create journal table permalink