Lock Types - Oracle Database Transactions and Locking Revealed - page 38

DDL Locks
DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other
sessions. For example, if I perform the DDL operation ALTER TABLE T , the table T will in general have an exclusive
DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table.

■ oracle 11 g has modified what used to be a rule. In the past, aLter taBLe t would have an exclusive
DDL lock placed against it. In this example, table t prevents other sessions from performing DDL and acquiring tM
locks (used to modify the contents of the table). Now, many aLter commands can be performed online—without
preventing modifications.

Note

DDL locks are held for the duration of the DDL statement and are released immediately afterward. This is done,
in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). For this reason,
DDL always commits in Oracle. Every CREATE , ALTER , and so on statement is really executed as shown in this pseudo-code:

Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;

So, DDL will always commit, even if it is unsuccessful. DDL starts by committing; be aware of this. It commits
first so that if it has to roll back, it will not roll back your transaction. If you execute DDL, it will make permanent any
outstanding work you have performed, even if the DDL is not successful. If you need to execute DDL, but you do not
want it to commit your existing transaction, you may use an autonomous transaction.
There are three types of DDL locks:
• Exclusive DDL locks : These prevent other sessions from gaining a DDL lock or TM (DML) lock
themselves. This means that you may query a table during a DDL operation, but you may not
modify it in any way.

• Share DDL locks : These protect the structure of the referenced object against modification by
other sessions, but allow modifications to the data.

• Breakable parse locks : These allow an object, such as a query plan cached in the shared pool,
to register its reliance on some other object. If you perform DDL against that object, Oracle
will review the list of objects that have registered their dependence and invalidate them.
Hence, these locks are breakable—they do not prevent the DDL from occurring.
Most DDL takes an exclusive DDL lock. If you issue a statement such as

Alter table t move;

the table T will be unavailable for modifications during the execution of that statement. The table may be queried
using SELECT during this time, but most other operations will be prevented, including all other DDL statements.
In Oracle, some DDL operations may now take place without DDL locks. For example, I can issue the following:

Create index t_idx on t(x) ONLINE;