ORA-04021: timeout occurred while waiting to lock object during truncate table or alter table

Hi,

Sometimes you can get ” ORA-04021: timeout occurred while waiting to lock object  ” during truncate table or alter table.

 

Details of error are as follows.

ALTER TABLE MSD.TEST_TABLE TRUNCATE SUBPARTITION SYS_SUBP2946291;

ALTER TABLE MSD.TEST_TABLE TRUNCATE SUBPARTITION SYS_SUBP2946291
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object MSD.TEST_TABLE

 

To solve this problem, you can use DDL_LOCK_TIMEOUT parameter with related query as follows.

ALTER SESSION SET DDL_LOCK_TIMEOUT=6000;
ALTER TABLE MSD.TEST_TABLE TRUNCATE SUBPARTITION SYS_SUBP2946291;

 

If you get the same error even though you use DDL_LOCK_TIMEOUT parameter, then you should use the following query to find the related session which hold this table.

SELECT a.inst_id,
status,
username,
machine,
event,
blocking_session,
sql_id,
last_call_et,
status,
'alter system kill session ''' || a.sid || ',' || serial# || ''';'
FROM gv$access a, gv$session b
WHERE a.inst_id = b.inst_id AND a.sid = b.sid
AND object = 'TEST_TABLE';

 

query result is as follows.

alter system kill session '489,46242,@5';
alter system kill session '824,41709,@4';
alter system kill session '877,15892,@7';

 

 

then execute this kill session script, and kill these 3 sessions which hold the TEST_TABLE table.

Once you kill the related sessions, you can alter the table as follows.

SQL> ALTER TABLE MSD.TEST_TABLE TRUNCATE SUBPARTITION SYS_SUBP2946291;

Table altered.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/