create index时对表加的什么锁

SQL> create index idx_1 on A02 (owner, object_name, subobject_name);

Index created

SQL> select sid from v$mystat where rownum<2;

SID
----------
16

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
TEST 16 Share A02 7 30 84
TEST 16 Row Exclusive OBJ$ 7 30 84

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
16 TX 458782 84 Exclusive 0 6 0
16 TM 18 0 Row Exclusive 0 6 0
16 TM 6319 0 Share 0 6 0

select owner,object_name,object_id from all_objects where object_id in (18,6319)
TEST A02 6319
SYS OBJ$ 18

可见在create index时会在A02表上加TM为Share的锁,在sys.obj$上加TM为Row Exclusive的锁，TX为Exclusive的锁。
如果在create index时A02上有DML，会首先在A02上加TM为Row Exclusive的锁，其与存在在A02上的TM为Share的锁不相容，所以会发生等待。
insert into a02 select * from a02 where rownum<2（SID=15）

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
TEST 15 None A02 0 0 0
TEST 16 Share A02 7 30 84
TEST 16 Row Exclusive OBJ$ 7 30 84

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
15 TM 6319 0 None 3 4 0
16 TX 458782 84 Exclusive 0 6 0
16 TM 18 0 Row Exclusive 0 6 0
16 TM 6319 0 Share 0 6 1

同样，如果TABLE上有DML操作，这时进行CREATE INDEX，会报错ORA-00054: resource busy and acquire with NOWAIT specified。
因为dml会在A02上加TM为Row Exclusive的锁，会阻塞create index时对A02加TM为Share的锁。

那为什么create index online不会阻塞dml呢？

SQL> drop index idx_1;

Index dropped

SQL> create index idx_1 on A02 (owner, object_name, subobject_name) online;

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

TEST 16 Row share A02 1 23 89
TEST 16 Share SYS_JOURNAL_6391 1 23 89

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

16 TX 65559 89 Exclusive 0 4 0
16 TM 6392 0 Share 0 4 0
16 TM 6319 0 Row share 0 4 0

可见在create index online时对A02加的是TM为Row share的锁，其与Row Exclusive是兼容的。同时对sys.obj$的锁消失。会产生一个SYS_JOURNAL_6391的表，用于create index online时DML操作产生的索引维护。