DDL_LOCK_TIMEOUT : DDL With the WAIT Option
DDL commands require exclusive locks on internal structures. If these locks are not available some commands return with an "ORA-00054: resource busy" error message, which can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes the
DDL_LOCK_TIMEOUT
parameter, which can be set at instance or session level using the
ALTER SYSTEM
and
ALTER SESSION
commands respectively.
The
DDL_LOCK_TIMEOUT
parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. Create a new table and insert a row, but don't commit the insert.
create table lock_tab (
id number
insert into lock_tab values (1);
Leave this session alone and in a new session modify the column. The "ORA-00054" error is returned immediately.
alter table lock_tab modify (
id number not null
alter table lock_tab modify (
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Set the
DDL_LOCK_TIMEOUT
at session level to a non-zero value and attempt to modify the column again. The result is the same, but it takes 30 seconds before the error is returned.
alter session set ddl_lock_timeout=30;
alter table lock_tab modify (
id number not null
alter table lock_tab modify (
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
If we repeat the
ALTER TABLE
command and commit the insert in the first session within 30 seconds, the
ALTER TABLE
will return a successful message.
alter table lock_tab modify (
id number not null
Table altered.
For more information see:
DDL_LOCK_TIMEOUT
DDL_LOCK_TIMEOUT : DDL With the WAIT Option
Hope this helps. Regards Tim...
Back to the Top.
