【DB笔试面试661】在Oracle中，在新建或重建索引时有哪些锁？

♣

题目部分

在Oracle中，在新建或重建索引时有哪些锁？

♣

答案部分

可以利用10704和10046事件跟踪新建或重建索引过程中的锁信息，命令为：

1alter session set events '10704 trace name context forever,level 10';
2alter session set events '10046 trace name context forever,level 12';

新建或重建索引的锁信息如下图所示：

图 5-15 新建或重建索引的锁信息

不带ONLINE的新建或重建索引的SQL语句获取的是4级TM锁，它会阻塞任何DML操作。

在Oracle 10g中，带ONLINE的新建或重建索引的SQL语句在开始和结束的时候获取的是4级TM锁，而在读取表数据的过程中获取的是2级TM锁，所以，在Oracle 10g中，即使加上ONLINE也会阻塞其它会话的DML操作。

在Oracle 11g中，带ONLINE的新建或重建索引的SQL语句在整个执行过程中获取的是2级TM锁，并不会阻塞其它会话的DML操作，但是在创建或重建索引的过程中，其它的会话产生的事务会阻塞索引的创建或重建操作，所以必须结束其它会话的事务才能让创建或重建索引的操作完成。所以应该避免在业务高峰期创建索引。

在Oracle 11g带ONLINE的新建或重建索引的情况下：

①　过程中会持有OD（ONLINE DDL）、DL（Direct Loader Index Creation）两种类型的锁，在Oracle 10g下只有DL锁没有OD锁。

②　表级锁TM的持有模式为2级RS（Row Share）与3级RX（Row Exclusive）类型的锁互相兼容，因此不会在表级发生阻塞。

③　阻塞发生在行级锁申请阶段，即请求的4级S（Share）类型的锁与执行DML的会话已经持有的6级X（Exclusive）锁之间存在不兼容的情况；相比非ONLINE方式的表级锁，锁的粒度上更加细化，副作用更小。

④　新增以“SYS_JOURNAL_”为前缀的IOT表，记录与索引创建动作同时进行的其它DML操作修改过的记录，等到索引创建完成前将IOT表里的记录合并至索引中并删除IOT表。

& 说明：

有关Oracle数据库锁的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2128896/

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗