resource busy and acquire with NOWAIT specified or timeout expired [message #673245] |
Tue, 13 November 2018 05:39 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have the LIST Partitioned table.We are performing some DML Operation into one partition and
exactly at the same time doing some DDL Operations on the another Partition .
But some times in UAT Environments it leading to that
resource busy and acquire with NOWAIT specified or timeout expired Error.
When we perform the DML Operations on partitioned table on one specific partition,
will it acquire the LOCK on the total table?
Can't we perform any DDL operation on another Partition ?
But I am unable to replicate the same in Dev Environment
Please help me to understand
Thanks
SaiPradyumn
|
|
|
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673247 is a reply to message #673245] |
Tue, 13 November 2018 06:25 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Simple test:
-- Session 1:
SQL> drop table t;
Table dropped.
SQL> create table t (val int)
2 partition by list (val)
3 (
4 partition t_1 values (1),
5 partition t_default values (default)
6 )
7 /
Table created.
SQL> insert into t values(1);
1 row created.
SQL> -- no commit
-- Session 2:
SQL> alter table t split partition t_default values (2) into (partition t_2, partition t_default);
Table altered.
So at least some DDL are permitted but not those that impact the whole table:
SQL> alter table t modify (val not null);
alter table t modify (val not null)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Locks held by session 1 are:
SQL> @lock
Sid Status User OS Pid LK Mod W Object
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,9 ACTIVE MICHEL 5008 TX X _SYSSMU5_863307317$ tx: 0x0005.0013.0000DCA9
TM RX MICHEL.T
TM RX MICHEL.T.T_1
An exclusive lock on a rollback segment (this is the transaction marker) and 2 row exclusive locks on the definition of T and T.T_1 so you can work on other partition(s) than T_1 but some of the operations that can be done may require a lock on the global table definition too.
Note that locks change with Oracle version, I used the one you posted for this test.
[Updated on: Tue, 13 November 2018 10:31] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|