//
you're reading...
Administration, Initialization Parameter

DDL_LOCK_TIMEOUT : Initialization parameter

What happen when you perform a DML operation and an another session performing a DDL operation on the same table?

Session I

 
hr@O11R2> INSERT INTO test VALUES (73964, 'ITEMS','TABLE');

1 row created.

Session II

 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

hr@O11R2> CREATE INDEX text_idx_oid ON test (object_id);
CREATE INDEX text_idx_oid ON test (object_id)
                             *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Oracle automatically places DML locks on the table that is being modified by a transaction. So if another session/user try to execute a DDL statement (CREATE/ALTER/DROP) against the table, the request will fail automatically with the error ORA-00054. Since DDL statement require exclusive locks on the table while changing table’s structure.

A work-around, repeatedly execute the command in order to acquire the locks your statement need.

Oracle 11g introduced a new parameter DDL_LOCK_TIMEOUT, which you can use to solve (work-around) this problem. This parameter controls the waiting time for a DDL statement to wait for acquire DML lock on the table.

The DDL_LOCK_TIMEOUT can have value range from 0 to 1,000,000 seconds (277.77 hours or 11-1/2 days) :eek:.

 
dbadmin@O11R2> SHOW PARAMETER ddl_lock

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

Default is 0, that is DDL statement won’t wait for DML locks.
You can set up this parameter at session level or system level.

In this example ddl_lock_timeout value set to 45 seconds.

 
hr@O11R2> ALTER SESSION SET ddl_lock_timeout = 45;

Session altered.

Elapsed: 00:00:00.01

hr@O11R2> CREATE INDEX text_idx_oid ON test (object_id);
CREATE INDEX text_idx_oid ON test (object_id)
                             *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Elapsed: 00:00:45.04
hr@O11R2>

The statement wait for 45 seconds before failing.

If the transaction in session 1 commited before the 45 seconds, it will release the lock on test table and DDL statement in session 2 will successfully execute.

Session I

 
hr@O11R2> INSERT INTO test VALUES (73964, 'ITEMS','TABLE');

1 row created.

Elapsed: 00:00:00.00
hr@O11R2> COMMIT;       -- commit completes.

Commit complete.

Elapsed: 00:00:00.00
hr@O11R2>

Session II

 
hr@O11R2> CREATE INDEX text_idx_oid ON test (object_id);

Index created.

Elapsed: 00:00:16.64
hr@O11R2>

Happy Learning.

Advertisements

Discussion

No comments 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: