Oracle, Troubleshooting

Oracle 11g: DDL WAIT Option (DDL_LOCK_TIMEOUT)

Hello,In Oracle 11g, the new initialization parameter DDL_LOCK_TIMEOUT controls the duration (in seconds) for which a DDL statement will wait for a DML lock.The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

The default value of zero indicates a status of NOWAIT.

SQL> show parameter DDL_LOCK_TIMEOUT;

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

If these locks are not available, the commands return with an “ORA-00054: resource busy” error message.

solutions:

  1.  kill oracle user session,
  2. Update DDL_LOCK_TIMEOUT
    SQL> set timing onSQL> alter session set ddl_lock_timeout=60;

    Session altered.
    Elapsed: 00:00:00.02

br Erman.

Advertisements
Standard

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s