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

The ONLINE keyword modifies the method by which the index is actually built. Instead of taking an exclusive
DDL lock, preventing modifications of data, Oracle will only attempt to acquire a low-level (mode 2) TM lock on the
table. This will effectively prevent other DDL from taking place, but it will allow DML to occur normally. Oracle
accomplishes this feat by keeping a record of modifications made to the table during the DDL statement and applying
these changes to the new index as it finishes the CREATE action. This greatly increases the availability of data. To see
this for yourself, you could create a table of some size:
EODA@ORA12CR1> create table t as select * from all_objects;
Table created.
EODA@ORA12CR1> select object_id from user_objects where object_name = 'T';
OBJECT_ID
----------
244277
And then run the create index against that table:
EODA@ORA12CR1> create index t_idx on t(owner,object_type,object_name) ONLINE;
While at the same time running this query in another session to see the locks taken against that newly created
table (remember, ID1=244277 is specific to my example, you'll want to use your object ID).
EODA@ORA12CR1> select (select username
2 from v$session
3 where sid = v$lock.sid) username,
4 sid,
5 id1,
6 id2,
7 lmode,
8 request, block, v$lock.type
9 from v$lock
10 where id1 = 244277
11 /
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
--------------- ---------- ---------- ---------- ---------- ---------- ---------- --
EODA 22 244277 0 3 0 0 DL
EODA 22 244277 0 3 0 0 DL
EODA 22 244277 0 2 0 0 TM
EODA 22 244277 0 4 0 0 OD
So, here we see four locks taken out against our object. The two DL locks are direct load locks. They are used to
prevent a direct path load into our base table while the index creation is taking place (which implies, of course, that
you cannot directly path load the table AND create the index simultaneously). The OD lock is a lock type first appeared
with Oracle 11 g (you would not see that lock in 10 g or 9 i ) that permits truly online DDL. In the past (10 g and before),
online DDL such as CREATE INDEX ONLINE was not 100 percent online. It would take a lock at the beginning and end
of the CREATE statement—preventing other concurrent activities (modifications of the base table data). It was mostly
online but not completely online . Starting with 11 g , the CREATE INDEX ONLINE command is completely online; it does
not require exclusionary locks at the beginning/end of the command. Part of the implementation to accomplish this
feat was the introduction of the OD (Online DDL) lock; it is used internally to allow truly online DDL operations.