Table Split and ORA-54: locking mechanism explained
(Doc ID 2248273.1)
Last updated on NOVEMBER 04, 2019
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
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 ?
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!