DDL Wait Option in Oracle.

DDL Wait Option in Oracle.
A DBA is trying to alter the table called SALES to add a column, TAX_CODE. he issues the
following SQL statement:

But instead of getting something like “Table altered”, he gets:

The error message says it all: the table is being used right now, probably by a transaction, so
getting an exclusive lock on the table may be next to impossible. Of course, the rows of the table
are not locked forever. When sessions perform commit the locks on those rows are released, but
before that unlock period gets very far, other sessions may update some other rows of the table—
and thus the slice of time to get the exclusive lock on the table vanishes.

In a typical business environment, the window for locking the table exclusively does open
periodically, but the DBA may not be able to perform the alter command exactly at that time. Of
course, the DBA can just keep on typing the same command over and over again until he gets an
exclusive lock—or goes nuts, whichever comes first.

In Oracle Database 11g, there is a better option: the DDL Wait option.

Now, when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation
until it’s successful or the time expires, whichever comes first. When he issues:

The statement hangs and does not error out. So, instead of DBA trying repeatedly to get the
elusive fraction of time when the exclusive lock is available, she outsources repeated trials to
Oracle Database 11g, somewhat like a telephone programmed to re-try a busy number.

This behavior is default so that they don’t need to issue the ALTER SESSION statement every
time. If you issue

ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10

The sessions automatically wait for that time period during DDL operations. Just like any other.

ALTER SYSTEM statement, this can be overridden by an ALTER SESSION statement.