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