ddl_lock_timeout to avoid ORA-00054

I have a previous article available here that explains about ORA-00054 and the reason of this error, and alto the enhancement starting 12c to avoid this error. In this article I would further explain ORA-00054 and how to use an initialization parameter DDL_LOCK_TIMEOUT to avoid this error. Instead of returning this error because of a lock on the object, your DDL would keep waiting for the locks to be released and get executed automatically when object is available for the DDL operation.

Blocking and non-Blocking DDLs 

Oracle has two types of DDLs: blocking, and non-blocking. DDL_LOCK_TIMEOUT only affects blocking DDL statements. Blocking DDLs would return ORA-00054 if table/index is already locked. DDL_LOCK_TIMEOUT parameter is used to stop blocking DDL to return ORA-00054 and wait until the seconds elapse as specified in DDL_LOCK_TIMEOUT parameter.

As explained above, DDL_LOCK_TIMEOUT is used to specify number of seconds the DDL would wait and keep trying its execution before returning ORA-00054. This feature is deven more helpful in the environments where tables/indexes are locked and unlocked very frequently and DBAs need to try executing a DDL again and again until it DDL gets executed successfully. To avoid this try try and keep trying, this feature lets you execute command only once.
Following is an example of how to use DDL_LOCK_TIMEOUT and how it works for blocking DDLs (remember that non-blocking DDLs do not need this parameter).

Session 1 SQL> create table test(id number); Table created. SQL> create index test_idx on test(id); Index created. SQL> insert into test values (1); 1 row created.

 In a different session, I would execute a non-blocking DDL to see how it goes.

Session 2 SQL> alter table test add (name varchar2(10));

Session 2 would keep waiting until the session 1 commits or rolls backs the DML. This statement would get executed automatically after COMMIT or ROLLBACK of DML from session 1.

Now let’s open another session and execute a blocking DDL and test parameter DDL_LOCK_TIMEOUT. A blocking DDL I am going to use in third session is DROP INDEX

Session 3 SQL>  drop index test_idx;  drop index test_ids ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

As you can see above, as soon as I execute DROP INDEX statement, it immediately returns ORA-00054 because session 1 has an unfinished DML on table TEST. Now let’s test DDL_LOCK_TIMEOUT in a new session, session 4 by setting this parameter’s value to 10 seconds at session level.

Session 4 SQL> alter session set ddl_lock_timeout=10; Session altered. SQL> set timing on SQL>  drop index ttt;  drop index ttt             * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:10.01

As you see above, the DDL did not return ORA-00054 immediately, rather it waited for 10 seconds during which it kept trying to execute this DDL, and eventually returned ORA-00054 after the timeout.

Now I will execute DDL again in session 4, and then go to session 1 and commit the DML, and we will see that as soon as I issue COMMIT in session 1, the DDL in session 4 would get executed (and of course also non-blocking DDL in hung state in session 3).

Session 4 SQL> drop index test_idx; Index dropped. Elapsed: 00:00:06.29

  This time DDL got executed after 6 seconds of execution because I issued a COMMIT in session 1.