Sunday, June 21, 2009

DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT

Whenever you perform any DDL operation on a table, oracle try to lock the table exclusively. In oracle version 10g and earlier, whenever you issue DDL, oracle try to lock the table exclusively with NOWAIT option and if it fails then immediately "ORA-00054 resource busy and acquire with NOWAIT specified" error returned. In fact, in oracle 10g we didn't have any other alternatives. So we had to wait until resource is free and in fact in production database you might need hours to complete your DDL jobs and you might need to try it frequently to test when resource become free.

A discussion about ORA-54 error with an example is demonstrated in the topic ORA-00054: resource busy and acquire with NOWAIT specified.

Oracle database 11g includes the DDL_LOCK_TIMEOUT parameter, which you can set using the ALTER SYSTEM and ALTER SESSION commands. Now you have the opportunity not to issue DDL frequently and query itself can wait a specified time and if it find resource free within this time DDL operation will be completed.

The parameter DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue.
- The default value is zero which indicates a status of NOWAIT.
- The maximum value is 1,000,000 seconds which indicates the DDL statement will wait forever to acquire a DML lock.
- If you specify time in the DDL_LOCK_TIMEOUT parameter and if a lock is not acquired before the timeout period expires, then an error is returned.

To demonstrate this scenario let's create a simple example in oracle database 11g.

1)Create table named test_wait, insert a row and leave the session alone.
SQL> create table test_wait(col1 number);

Table created.

SQL> insert into test_wait values(1);

1 row created.

2)Now from another session do DDL operation.
Note that you may be succeed while adding column to the table in this case. Here in another session I am practising DROP DDL command.
SQL> drop table test_wait;
drop table test_wait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> show parameter ddl_lock_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0

It failed because in the above you see ddl_lock_timeout is set to 0 which means NOWAIT and so if query see someone lock the row error returned immediately.

Now we change to parameter to 100. Now query will wait for 100 seconds in order to see whenever resource become free.

SQL> alter session set ddl_lock_timeout=100;

Session altered.

Now in the first session issue commit.
SQL> commit;

And in the second session try to drop the table and you see drop become successful if you do commit within 100 seconds.
SQL> drop table test_wait;

Table dropped.

However if you did not do commit within 100 seconds in the first session then after 100 seconds in the second session DROP command would fail with "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".

Related Documents
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2009/06/new-features-in-oracle-database.html

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)