在维护索引过程中，需要获取表上的TM锁，情况如下：
* create index/alter index rebuild操作：
持有表上mode=4的TM锁
* create index online/alter index rebuild online操作：
依次获取表上如下TM锁：mode=2的TM->mode=4的TM->mode=2的TM->mode=4的TM

说明：online操作时两次短时间获取mode=4的TM锁，分别用于：
第一次是等待当前表上所有进行中的DML操作事务结束，为开始创建索引取得一致点；
第二次是为了将索引创建过程中该表上的变化（jernal）合并到表中，取得一致点。

测试如下：

1,创建测试表
[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 20 10:00:23 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(38)

SQL> drop table t;

Table dropped.

SQL> create table t
2 as
3 select * from dba_users;

Table created.

SQL> select object_id,to_char(object_id,'xxxxxxx') from dba_objects where wner=user and object_name='T';

OBJECT_ID TO_CHAR(
---------- --------
155087 25dcf

2,create index 持有表上的锁
SQL> alter session set events '10704 trace name context forever,level 12';

Session altered.

SQL>
SQL> create index idx_t_username on t(username);

Index created.

SQL>
SQL> alter session set events '10704 trace name context off';

Session altered.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls
bnet_ora_923.trc

--分析跟踪文件
==〉该DDL获取表上mode=4的TM锁
*** 2013-02-20 10:04:55.448
ksqgtl *** TM-00025dcf-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3d7925a8, ktcdix=2147483647, topxcb=0x0x3d7925a8
ktcipt(topxcb)=0x0
*** 2013-02-20 10:04:55.527
ksqrcl: TM,25dcf,0
ksqrcl: returns 0

3,create index online持有表上的锁

[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 20 10:05:51 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> alter session set events '10704 trace name context forever,level 12';

Session altered.

SQL> create index idx_t_userid on t(user_id) online;

Index created.

SQL> alter session set events '10704 trace name context off';

Session altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls
bnet_ora_1695.trc

--分析跟踪文件
==〉该DDL依次获取表上如下TM锁：mode=2的TM->mode=4的TM->mode=2的TM->mode=4的TM
*** 2013-02-20 10:06:15.741
ksqgtl *** TM-00025dcf-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3d7af0b0, ktcdix=2147483647, topxcb=0x0x3d7af0b0
ktcipt(topxcb)=0x0
*** 2013-02-20 10:06:15.869
ksqcnv: TM-00025dcf,00000000 mode=4 timeout=21474836
*** 2013-02-20 10:06:15.869
ksqcmi: TM,25dcf,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:06:15.869
ksqcnv: TM-00025dcf,00000000 mode=2 timeout=21474836
*** 2013-02-20 10:06:15.869
ksqcmi: TM,25dcf,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:06:15.884
ksqcnv: TM-00025dcf,00000000 mode=4 timeout=21474836
*** 2013-02-20 10:06:15.884
ksqcmi: TM,25dcf,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:06:16.320
ksqrcl: TM,25dcf,0
ksqrcl: returns 0

4,alter index rebuild持有表上的锁

[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 20 10:06:31 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set events '10704 trace name context forever,level 12';

Session altered.

SQL> alter index idx_t_username on t(username) rebuild;
alter index idx_t_username on t(username) rebuild
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> alter index idx_t_username rebuild;

Index altered.

SQL> alter session set events '10704 trace name context off';

Session altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 60695 Feb 20 10:07 bnet_ora_1799.trc

--分析跟踪文件
==〉该DDL获取表上mode=4的TM锁
*** 2013-02-20 10:07:13.303
ksqgtl *** TM-00025dcf-00000000 mode=4 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x0x3db6ab84, ktcdix=2147483647, topxcb=0x0x3db6ab84
ktcipt(topxcb)=0x0
*** 2013-02-20 10:07:13.324
ksqrcl: TM,25dcf,0
ksqrcl: returns 0

5,alter index rebuild online持有表上的锁

[oracle@bnet95 udump]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 20 10:07:21 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set events '10704 trace name context forever,level 12';

Session altered.

SQL>
SQL> alter index idx_t_username rebuild online;

Index altered.

SQL>
SQL> alter session set events '10704 trace name context off';

Session altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@bnet95 udump]$ ls -lrt
-rw-r----- 1 oracle oinstall 60695 Feb 20 10:07 bnet_ora_1917.trc

--分析跟踪文件
==〉该DDL依次获取表上如下TM锁：mode=2的TM->mode=4的TM->mode=2的TM->mode=4的TM
*** 2013-02-20 10:07:34.867
ksqgtl *** TM-00025dcf-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x0x3db6ab84, ktcdix=2147483647, topxcb=0x0x3db6ab84
ktcipt(topxcb)=0x0
*** 2013-02-20 10:07:34.909
ksqcnv: TM-00025dcf,00000000 mode=4 timeout=21474836
*** 2013-02-20 10:07:34.909
ksqcmi: TM,25dcf,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:07:34.909
ksqcnv: TM-00025dcf,00000000 mode=2 timeout=21474836
*** 2013-02-20 10:07:34.909
ksqcmi: TM,25dcf,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:07:34.914
ksqcnv: TM-00025dcf,00000000 mode=4 timeout=21474836
*** 2013-02-20 10:07:34.914
ksqcmi: TM,25dcf,0 mode=4 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0
*** 2013-02-20 10:07:34.985
ksqrcl: TM,25dcf,0
ksqrcl: returns 0

ITPub博客

索引维护时持有表上的TM锁

最新文章