DDL_LOCK_TIMEOUT Parameter

The DDL (Data Definition Language) commands required to put an exclusive lock on internal structure of the database. If when trying to configure one we  cannot must to another user / process has one yet, we will receive the following message;

ORA-00054: resource busy

To try to reduce this have basically two options, one is launching the command repeatedly, until we get lucky and run (better known as pooling) or use the parameter DDL_LOCK_TIMEOUT. This parameter indicates the number of seconds a DDL statement should wait to get set the lock, before we get an ORA-00054 error.

You can set the parameter level DDL_LOCK_TIMEOUT at session or system level. You can see below an example;

 
SQL> REM
SQL> REM Set DDL_LOCK_TIMEOUT to 60 seconds
SQL> REM
SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;

Session altered.

SQL> REM
SQL> REM The next alter will wait for one minute, after that it get a ORA-00054
SQL> REM
SQL> ALTER TABLE EXEMPLE_ANR ADD (NEW_COLUMN VARCHAR2(10));

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>

HTH – Antonio NAVARRO

Advertisements

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