Systems Engineering and RDBMS

DDL_LOCK_TIMEOUT – New wait option in Oracle 11g

Posted by decipherinfosys on March 5, 2009

Normally, any DDL changes are planned well in advance to put in production.  But in a development/QA/test environments, DBA can take some liberty and make changes even when schema is being actively used.  Let us assume that the DBA wants to issue an alter command for a table to drop a column. This is a DDL statement and it acquires exclusive lock on the structure.  Now suppose that when the DBA issued the alter command, some user was already working on the table to update some rows and hasn’t perform the commit yet.  Alter command will also try to acquire an exclusive lock on the table and will fail to acquire lock, as exclusive and shared locks have been place on the table by the user’s session.  In this scenario, the alter statement will fail with a ‘resource busy’ error.  If the table is actively used, it is very difficult to get that small window to apply the DDL changes.

In 11g, oracle introduced a new initialization parameter DDL_LOCK_TIMEOUT to set up the lock wait option for DDL commands.  We can change it either at session level or at system level. When this parameter is set to non zero value, DDL command will wait for that many seconds before throwing ORA-00054 (Resource busy) error.

We already have a table called test in schema with some data in it.   We will alter it to drop an existing column with this new parameter defined. Connect to SQL*Plus and issue following update statement. Feel free to change the table name and column names as per your schema.

UPDATE TEST
SET col2 = ‘TABLE’
WHERE col1 = ‘TEST’;

Keep this session open and connect to other session to alter the table. Statement will error out immediately.

SQL> set timi on
SQL> ALTER TABLE TEST DROP COLUMN COL3;
ALTER TABLE TEST DROP COLUMN COL3
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Elapsed: 00:00:00.01

As of now DDL lock wait option is not set. Now let us alter the session, the one from which we issued the alter command to set the value to 15 seconds

SQL> ALTER SESSION SET ddl_lock_timeout = 15;

If we issue alter table statement again, it will not fail immediately instead it will keep trying to get an exclusive lock and execute the command for next 15 seconds and if it fails to obtain lock it will return an error.

SQL> ALTER TABLE TEST DROP COLUMN COL3;
ALTER TABLE TEST DROP COLUMN COL3
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Elapsed: 00:00:15.00

Now let us issue the same alter statement again. After few seconds or so, go to previous session from which we fired the update statement and commit the change. Once the change is committed, alter table command will go through fine from other session.

SQL> ALTER TABLE TEST DROP COLUMN COL3;

Table altered.

Elapsed: 00:00:09.03

This feature can be very useful when we have to put the schema changes in development or test environments especially when development efforts are in full swing.

Resources:

  • Oracle 11g New Features Article – here.

One Response to “DDL_LOCK_TIMEOUT – New wait option in Oracle 11g”

  1. Reddy.D said

    Sry sir , it is not working in my system, can you help me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: