数据库版本信息：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition
Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 -
Production
NLSRTL Version 11.2.0.4.0 - Production
创建索引的锁行为
SESSION1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
27
SESSION2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
125
SESSION1：
SQL> insert into lixia.test values(4,'test4');
1 row created.
SESSION3：
查看锁信息
SQL> select sid,type,id1,id2,lmode from v$lock where sid in (125,27);
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
125 AE 100 0 4
125 TO 79833 1 3
27 AE 100 0 4
27 TM 170376 0 3
27 TX 589839 5469 6
SESSION2（SID 为27）的会话持有排他模式的TX锁和RX（行级排他锁）模式的表锁。在RX模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，但是不允许
其他事务对相同的表添加排他锁（X锁)
SESSION2：
SQL> alter session set tracefile_identifier='create_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index lixia.idx_test_id on lixia.test(id);
create index lixia.idx_test_id on lixia.test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set events '10046 trace name context off';
创建索引索引失败，提示资源忙。
查看10046追踪文件，发现在创建索引时需要先对表添加表级共享锁（S锁）。
=====================
PARSING IN CURSOR #139950258093056 len=48 dep=1 uid=0 oct=26 lid=0 tim=1445518323040883 hv=439152460 ad='884c06f8' sqlid='6c12yxnd2tvuc'
LOCK TABLE "LIXIA"."TEST" IN SHARE MODE NOWAIT
END OF STMT
PARSE #139950258093056:c=0,e=274,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445518323040882
EXEC #139950258093056:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445518323040951
ERROR #139950258093056:err=54 tim=1445518323040961
CLOSE #139950258093056:c=0,e=3,dep=1,type=0,tim=1445518323040989
=====================
PARSE ERROR #139950256770136:len=48 dep=0 uid=0 oct=9 lid=0 tim=1445518323041052 err=54
create index lixia.idx_test_id on lixia.test(id)
SESSION1：
提交事务
SQL> commit;
Commit complete.
SESSION2:
创建索引成功。
SQL> create index lixia.idx_test_id on lixia.test(id);
Index created.
======================================================================
REBUILD 索引的锁行为
SESSION1：
SQL> insert into lixia.test values(5,'test5');
1 row created.
SESSION2：
查看锁信息。
SQL> select sid,type,id1,id2,lmode from v$lock where sid in (125,27);
SID TY ID1 ID2 LMODE
---------- -- ---------- ---------- ----------
125 AE 100 0 4
125 TO 79833 1 3
27 AE 100 0 4
27 TM 170376 0 3
27 TX 589848 5472 6
SESSION2（SID 为27）的会话持有排他模式的TX锁和RX（行级排他锁）模式的表锁。
SESSION2：
SQL> ALTER INDEX lixia.idx_test_id REBUILD;
ALTER INDEX lixia.idx_test_id REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set tracefile_identifier='rebuild_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> ALTER INDEX lixia.idx_test_id REBUILD;
ALTER INDEX lixia.idx_test_id REBUILD
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL>
SQL> alter session set events '10046 trace name context off';
Session altered.
[root@oradb ~]# cd /app/oracle/diag/rdbms/dilas1/dilas1/trace
[root@oradb trace]# more dilas1_ora_2631_rebuild_index.trc
=====================
PARSING IN CURSOR #139950257818784 len=66 dep=1 uid=0 oct=26 lid=0 tim=1445517412599506 hv=4081004650 ad='87735708' sqlid='6fhc8krtmyc3a'
LOCK TABLE FOR INDEX "LIXIA"."IDX_TEST_ID" IN SHARE MODE NOWAIT
END OF STMT
PARSE #139950257818784:c=3998,e=121530,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445517412599506
EXEC #139950257818784:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445517412599572
ERROR #139950257818784:err=54 tim=1445517412599581
CLOSE #139950257818784:c=0,e=2,dep=1,type=0,tim=1445517412599653
=====================
PARSE ERROR #139950257819728:len=37 dep=0 uid=0 oct=9 lid=0 tim=1445517412599682 err=54
ALTER INDEX lixia.idx_test_id REBUILD
查看10046的追踪文件在继续索引REBUILD时有对表添加共享模式的锁。
=====================================================================
删除索引的锁行为
在SESSION2对TEST 表持有TX锁和RX 模式的TM锁的情况下，删除索引。
SQL> alter session set tracefile_identifier='drop_index';
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> drop index lixia.idx_test_id;
drop index lixia.idx_test_id
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set events '10046 trace name context off';
Session altered.
查看10046追踪文件，发现在删除索引时会对表添加排他模式的表锁。
PARSING IN CURSOR #139950258171424 len=70 dep=1 uid=0 oct=26 lid=0 tim=1445517922051712 hv=2912218121 ad='884e9760' sqlid='9q373jyqt9v09'
LOCK TABLE FOR INDEX "LIXIA"."IDX_TEST_ID" IN EXCLUSIVE MODE NOWAIT
END OF STMT
PARSE #139950258171424:c=1000,e=1058,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1445517922051711
EXEC #139950258171424:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1445517922051774
ERROR #139950258171424:err=54 tim=1445517922051783
CLOSE #139950258171424:c=0,e=2,dep=1,type=0,tim=1445517922051809
=====================
PARSE ERROR #139950258695176:len=28 dep=0 uid=0 oct=10 lid=0 tim=1445517922051824 err=54
drop index lixia.idx_test_id