Table Split and ORA-54: locking mechanism explained (Doc ID 2248273.1)

Last updated on APRIL 25, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Goal

 The purpose of this document is to clarify the behaviour of the locking mechanism with DDL functions.  We use a real world example to illustrate it.

The customer runs an application that stores PDF documents into a table containing a BLOB (Securefile) field. The table is partitioned by List for Application ID and it's Subpartitioned by Range for Doc_IDs.

They have different tablespaces for each Subpartition. When the tablespace becomes 95% full then there is a batch process that executes an "Alter Table ... Split Subpartition", leaving the "old" subpartition on the current tablespace and the "new" subpartition is created on a "new" empty tablespace.

Because batch processes can be executed at any time (depending only on the capacity of tablespace and triggered when capacity is over 95%), the "Alter Table ... Split Subpartition" can clash with normal transactional activity on the table. Then, sometimes, the Split operation fails with an ORA-54 "resource busy and acquire with NOWAIT specified or timeout expired" error.

But what would happen to the transactional activity while the process executing the Split subpartition? Could it be waiting for the Split execution, for example, until the completion of the timeout? Could be any kind of locking affecting to the transactional activity due to the Split waiting in its timeout ?

Solution

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