Alter Index Rebuild Online Fails With ORA-60 When Insert Causes Interval Partition Added Meantime

(Doc ID 2384086.1)

Last updated on APRIL 16, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

Sat Mar 03 02:42:18 2018
ORA-00060: deadlock resolved; details in file /opt/oracle/diag/rdbms/xxxx/xxx/trace/xxxx_ora_22348.trc
online index (re)build cleanup: objn=1182386 maxretry=2000 forever=0
Sat Mar 03 02:42:28 2018
... ...
TABLE xxxx.xxxxx: ADDED INTERVAL PARTITION SYS_Pxxxx (5176) VALUES LESS THAN (TIMESTAMP' 2030-03-04 00:00:00'
0: waiting for 'library cache lock'
       handle address=0x95e781ad8, lock address=0x29730effb0, 100*mode+namespace=0x4f27900010003
       wait_id=131260 seq_num=197 snap_id=1
       wait times: snap=3.288358 sec, exc=3.288358 sec, total=3.288358 sec
       wait times: max=15 min 0 sec, heur=3.288358 sec
       wait counts: calls=2 os=2
       in_wait=1 iflags=0x55a2
    There is at least one session blocking this session.
     Dumping first 3 direct blockers:
       inst: 1, sid: 6027, ser: 15318
       inst: 1, sid: 5679, ser: 38017
       inst: 1, sid: 905, ser: 23964
     Dumping final blocker:
       inst: 1, sid: 6027, ser: 15318 *<<<<------------
   There are 83 sessions blocked by this session.
   Dumping one waiter:
     inst: 1, sid: 6027, ser: 15318 *<<<<------------
     wait event: 'row cache lock'
       p1: 'cache id'=0x8
       p2: 'mode'=0x0
       p3: 'request'=0x5
     row_wait_obj#: 2, block#: 180362644, row#: 0, file# 5
     min_blocked_time: 9403 secs, waiter_cache_ver: 1557

... ..
       LibraryObjectLock:  Address=29730effb0 Handle=95e781ad8 RequestMode=X *<<<<----------
         CanBeBrokenCount=32579 Incarnation=1363 ExecutionCount=0
         
         User=a8391f790 Session=a8391f790 ReferenceCount=0
         Flags=[0000] SavepointNum=1b8246
       LibraryHandle:  Address=95e781ad8 Hash=71f0b674 LockMode=X PinMode=X LoadLockMode=0 Status=VALD
         ObjectName:  Name=xxxx.xxxxx *<<<<----------
... ...
       SO: 0x287ef16748, type: 90, owner: 0xa77073400, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
        proc=0xa633e5b38, name=row cache enqueues, file=kqr.h LINE:2122 ID:, pg=0 conuid=0
       row cache enqueue: count=1 session=a8391f790 object=84ab0bef0, mode=S
       savepoint=0x1b786b
       row cache parent object: addr=84ab0bef0 cid=8(dc_objects) conid=0 conuid=0
       hash=81c861f6 typ=11 transaction=0 flags=00000002 inc=1, pdbinc=1
       objectno=1182386 ownerid=61 nsp=4 *<<<<----------
       name=xxxxx
       own=84ab0bfc0[287ef167f8,287ef167f8] wat=84ab0bfd0[2f9149ab30,2f9149ab30] mode=S *<<<<----------
 DEADLOCK DETECTED (id=0x8edb1fa6)

Chain Signature: 'library cache lock'<='row cache lock' (cycle)
Chain Signature Hash: 0x52a8007d


------------------------------------------------------------------------------
   Oracle session identified by:
   {
               instance: 1 (mopsdb01.mopsdb01)
                  os id: 22535
             process id: 702, oracle@pdbs02
             session id: 6027
       session serial #: 15318
   }
   is waiting for 'row cache lock' with wait info:
   {
                     p1: 'cache id'=0x8
                     p2: 'mode'=0x0
                     p3: 'request'=0x5
           time in wait: 156 min 48 sec
          timeout after: never
                wait id: 4890367
               blocking: 83 sessions
            current sql: insert into xxxx ... ...
           wait history:
... ...
   }
   and is blocked by
=> Oracle session identified by:
   {
               instance: 1 (mopsdb01.mopsdb01)
                  os id: 22348
             process id: 242, oracle@pdbs02 (TNS V1-V3)
             session id: 878
       session serial #: 19853
   }
   which is waiting for 'library cache lock' with wait info:
   {
                     p1: 'handle address'=0x95e781ad8
                     p2: 'lock address'=0x29730effb0
                     p3: '100*mode+namespace'=0x4f27900010003
           time in wait: 3.087087 sec
          timeout after: 14 min 56 sec
                wait id: 131260
               blocking: 83 sessions
            current sql: ALTER INDEX xxxx.xxxx REBUILD ONLINE PARALLEL 2
           wait history:
... ...
    }
   and is blocked by the session at the start of the chain.
 1. Data prepare:

grant dba to t49 identified by t49;
conn t49/t49
create table TIP (COL1 number not null, COL2 date, COL3 number, COL4 char(1000))
  partition by range(COL2) interval(numtodsinterval(1, 'day'))
    (partition P0 values less than (to_date('2018/04/09', 'YYYY/MM/DD'))) ;

begin
  for i in 1..10000 loop
    insert into TIP values(i,sysdate+i,i,'test'||i);
    commit;
  end loop;
end;
/

create index idx1 on tip (col1,col2);

2. Problem reproduce:

Connect 2 sessions, and session 1 executes rebuild index online statement, and session 2 executes insert statement with values that could cause interval partition added. And the execution of session 1 is slightly ahead than session2.

session 1:

alter index idx1 rebuild online;

session 2:

insert into TIP values (10001,sysdate+10001,10001,'a');
commit;

  And session 1 will fail with ORA-00060 error as following:

SQL> alter index idx1 rebuild online;
alter index idx1 rebuild online
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

        And session 2 will execute successfully, and interval partition will be added eventually.

SQL> insert into TIP values (10001,sysdate+10001,10001,'a');

1 row created.

SQL> rollback;

Rollback complete.

  

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms