DDL_LOCK_TIMEOUT Parameter Usage to Avoid ORA-00054: resource busy error

I will explain the DDL_LOCK_TIMEOUT Parameter Usage to Avoid ORA-00054: resource busy error in this post. let’s review the DDL_LOCK_TIMEOUT Parameter | ORA-00054: resource busy error.

 

 

DDL_LOCK_TIMEOUT Parameter | ORA-00054: resource busy error

You can get the ORA-00054: resource busy error during DDL Operation as follows.

SQL>truncate table test_table;
truncate table test_table
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:00.12

 

 

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

If you don’t want to get the ORA-00054: resource busy error, then you need to use DDL_LOCK_TIMEOUT Parameter Usage to Avoid ORA-00054: resource busy error as follows.

 

SQL> show parameter ddl_lock

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
SQL>




SQL> alter session set ddl_lock_timeout=600;

Session altered.

 

When you use the DDL_LOCK_TIMEOUT parameter, then DDL statements will wait for a DML lock.

If you set DDL_LOCK_TIMEOUT parameter, then Oracle will wait until DDL_LOCK_TIMEOUT value ( seconds ), then will perform the same operation.

 

You can set this parameter both System based and Session based as follows.

Session based

SQL> alter session set ddl_lock_timeout=600;

Session altered.

 

System based

SQL> alter system set ddl_lock_timeout=600 scope=both sid='*';

Session altered.

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database