ABORT_TABLE_REDF Not Releasing Locks (Doc ID 1914644.1)

Last updated on DECEMBER 06, 2016

Applies to:

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

Symptoms

After executing abort_redef_table procedure, session keeps holding library cache lock and TM lock on original table even though it finished correctly. It also blocks other sessions from running DMLs.

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('SCOTT','MAIN_TABLE','TEMP_TABLE');
  --> It will finish within a few seconds.

From Errorstack:

  PROCESS 32:
   :
  LIBRARY OBJECT LOCK: 0x26ffb414 handle=0x24ead18c mod=X
  pnc=(nil) pns=(nil) cbb=12 rpr=6 exc=0 ilh=(nil) ctx=(nil)
  use=0x2bc8ed78 ses=0x2bc8ed78 cnt=1 flg=CNB/[0001] spn=0x939
  LIBRARY HANDLE:0x24ead18c bid=43795 hid=e7beab13 lmd=X pmd=X sta=VALD
  name=SCOTT.MAIN_TABLE  
   :
   :
  (enqueue) TM-0001C7D1-00000000 DID: 0001-0020-000014D4
  lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
  mode: X, lock_flag: 0x0, lock: 0x2ac3c810, res: 0x2b87a288

  --> Holding library cache lock and TM lock on MAIN_TABLE
      in exclusive mode.

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