Database Reference
In-Depth Information
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;
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
