Finally, with Oracle 11g comes a solution to the dreaded "ORA-00054: resource busy and acquire with NOWAIT specified" message. It's now possible to specify how long the command should wait before timing out (either at the system or session level) by specifying a value in seconds for the DDL_LOCK_TIMEOUT parameter.
Permanently, at the system level:
alter system set DDL_LOCK_TIMEOUT=300 scope=both;
Temporarily, at the session level:
alter session set DDL_LOCK_TIMEOUT = 300;
Both of the above commands will set the timeout to 5 minutes (300 seconds), which can be tested / demonstrated with the aid of two sessions:
StepSession #1Session #2
| Session #1 | Session #2 | |
|---|---|---|
| 1 | SQL> create table test_table ( val number ); Table created. SQL> insert into test_table values (1); 1 row created. |
|
| 2 | SQL> set timing on | |
| 3 | SQL> create index test_idx on test_table (val); create index test_idx on test_table (val) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:00.09 |
|
| 4 | SQL> alter session set DDL_LOCK_TIMEOUT=300; | |
| 5 | SQL> create index test_idx on test_table (val); create index test_idx on test_table (val) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:05:00.68 |
|
| 6 | SQL> commit; Commit complete. |
You can see from the highlighted output above that the first attempt to create an index immediately errored whereas the second attempt waited for the 5 minutes specified via DDL_LOCK_TIMEOUT before the error message was displayed.