Home > oracle_11g_new_features > DDL_LOCK_TIMEOUT — new 11g wait parameter

DDL_LOCK_TIMEOUT — new 11g wait parameter

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

If a lock is not acquired before the timeout period expires, then an error is returned.

SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000

Elapsed: 00:00:00.00

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 26 20:19:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter ddl_lock_timeout

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

As of now DDL lock wait option is not set.

 

SQL> create table ddl_lock_timeout_test (CUSTOMER_ID number(10),
2  CUSTOMER_NAME varchar2(20));

Table created.

SQL> select * from ddl_lock_timeout_test;

CUSTOMER_ID CUSTOMER_NAME
———– ——————–
1 UMA

open a one more session and update the existing row.

SQL> update ddl_lock_timeout_test set CUSTOMER_NAME=’UMA KATRU’ where CUSTOMER_ID=1;

1 row updated.

alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

 

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

SQL> set timing on
SQL> alter session set ddl_lock_timeout=60;

Session altered.

Elapsed: 00:00:00.02

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 60 seconds and if it fails to obtain lock it will return an error.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);
alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:59.02

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> commit;

Commit complete.

SQL> alter table ddl_lock_timeout_test modify CUSTOMER_NAME varchar2(20);

Table altered.

Elapsed: 00:00:16.34

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: