简介：
黄廷忠，电信行业资深专家，超过10年专职电信行业 Oracle 数据库管理和运维经验，熟悉电信行业的业务、数据库及硬件架构。擅长数据库各种迁移方法、优化、疑难故障排除、数据库异常恢复等。
在 12.2 之前，对索引的创建和修改已经实现在线操作，但是在线删除索引功能在 12.2 中才出来。在线删除索引有什么作用，个人感觉作用不大，基本上，生产环境中我们很少会删除索引信息，也有可能是在 12C 之前，对索引的使用监控没有一个好的办法，我们不能确定哪些索引需要使用，哪些索引不使用，所以不敢删除。对索引是否删除，作为一个运维 DBA 而非开发 DBA，对索引认为“存在即是合理”。在 12.2 之前，对索引的 DDL 语句会导致游标失效，但是在 12.2 中引入了新的选项，可以选择 DDL 是否让相关游标失效。
下面测试在线删除索引需要获取哪些锁信息。
1.数据库版本信息
BANNER CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production 0
PL/SQL Release 12.2.0.1.0 – Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 – Production 0
NLSRTL Version 12.2.0.1.0 – Production
数据库版本是12.2.0.1，没有安装任何的补丁信息。
2.环境准备
创建测试表和测试索引。
SQL> create table drop_index as select * from dba_objects;
Table created.
SQL> create index ind_drop_index_1 on drop_index(object_id);
Index created.
3.测试在线删除索引获取的锁信息
在 12.2 中，TRACE 会话锁的 EVENT 已经发生了变化，使用 trace[ksq] 来实现，不要使用之前的 EVENT 10904。
SQL> alter session set events='trace[ksq] disk medium';
Session altered.
SQL> drop index ind_drop_index_1 online;
Index dropped.
SQL> select to_char(object_id,'xxxxxxx') from dba_objects where object_name=upper('drop_index');
TO_CHAR(OBJECT_I
—————-
11f45
通过 TRACE 文件可以看到在表上申请的 TM 为2级别的锁。不是原来的 TM 为6级别的锁。
[oracle@db5 trace]$ grep ksqgtlctx htz_ora_6304.trc|grep "\*\*\*"|grep -i "11f45"
2017-03-21 06:55:47.293*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***
2017-03-21 06:55:47.298*:ksq.c@9033:ksqgtlctx(): *** OD-00011F45-00000000-00000000-00000000 mode=4 flags=0x10401 why=269 timeout=0 ***
2017-03-21 06:55:47.298*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***
上面只给出在索引所在表上的 TM 级别锁，可以看到在表级别上面是申请的 MODE=2 级别的锁，不再是原来的 MODE=6 级别的锁，也就意味着 DML 语句可以正常执行。
4.在线删除索引对 DML 语句的影响。
下面一共4个会话，会话1模拟 DML 事务，会话2执行 drop index online，会话3模拟 DML 事务，回话4查询锁的信息。
会话1
SQL> delete drop_index where object_id=10;
1 row deleted.
会话2
SQL> alter session set events='trace[ksq] disk medium';
Session altered.
SQL> drop index ind_drop_index_1 online;
这里 HANG 住了
会话3
SQL> delete drop_index where object_id=1001;
1 row deleted.
这里任然可以进行 DML 语句，所以对后面的业务没有任何的影响。
查询会话锁的信息
SQL> @lock.sql
Enter value for sid: 58 会话1
SQL_ID
sid:serial:os sessio COMMAND OBJECT_NAME HASH_VALUE SQL_CHILD_NUMBER ID1-ID2 LMODE REQUEST TYPE CTIME
——————– ————— —————————— ———- —————– ——————– ——————– ————— ————————- ——-
1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 655377-796 6||Exclusive Transaction enqueue lock 534
1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 73541-0 3||Row Exclusive DML enqueue lock 534
1.58.534.158 BACKGROUND None 2564605132 980yabycdtj6c: 133-0 4||Share Other type 575
SQL> @lock.sql
Enter value for sid: 57 会话2
SQL_ID
sid:serial:os sessio COMMAND OBJECT_NAME HASH_VALUE SQL_CHILD_NUMBER ID1-ID2 LMODE REQUEST TYPE CTIME
——————– ————— —————————— ———- —————– ——————– ——————– ————— ————————- ——-
1.57.505.40 DROP INDEX HTZ.IND_DROP_INDEX_1 900803336 9xb91y0uv2as8:0 73545-0 6||Exclusive Other type 505
1.57.505.40 DROP INDEX Rollback Segment 900803336 9xb91y0uv2as8:0 327698-768 6||Exclusive Transaction enqueue lock 505
1.57.505.40 DROP INDEX HTZ.DROP_INDEX 900803336 9xb91y0uv2as8:0 73541-0 2||Row Share DML enqueue lock 505
1.57.505.40 DROP INDEX HTZ.DROP_INDEX 900803336 9xb91y0uv2as8:0 73541-0 4||Share Other type 505
1.57.505.40 DROP INDEX SYS.ORA$BASE 900803336 9xb91y0uv2as8:0 133-0 4||Share Other type 522
但是这里很奇怪的就是在 lock 中，没有找到会话2申请锁的信息，但是 drop 进程后台日志里面可以看到申请 TX 锁失败，一直在重复申请。
*** 2017-03-21T07:22:19.310314+08:00
2017-03-21 07:22:19.309*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-00000000
2017-03-21 07:23:19.358*:ksq.c@8219:ksqcmi(): performing local deadlock detection on TX-000A0011-0000031C-00000000-00000000
*** 2017-03-21T07:23:19.359224+08:00
2017-03-21 07:23:19.358*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-00000000
2017-03-21 07:24:19.371*:ksq.c@8219:ksqcmi(): performing local deadlock detection on TX-000A0011-0000031C-00000000-00000000
*** 2017-03-21T07:24:19.372349+08:00
2017-03-21 07:24:19.371*:ksq.c@8249:ksqcmi(): deadlock not detected on TX-000A0011-0000031C-00000000-0000000
下面查看整个锁的申请流程：
[oracle@db5 trace]$ grep ksqgtlctx htz_ora_6507.trc|grep "\*\*\*"
2017-03-21 07:12:21.089*:ksq.c@9033:ksqgtlctx(): *** CU-64E51B40-00000000-00000000-00000000 mode=6 flags=0x10000 why=231 timeout=300 ***
2017-03-21 07:12:21.090*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***
2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** SE-00000039-00008453-00000000-00000000 mode=4 flags=0x0 why=299 timeout=21474836 ***
2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** OD-00011F45-00000000-00000000-00000000 mode=4 flags=0x10401 why=269 timeout=0 ***
2017-03-21 07:12:21.093*:ksq.c@9033:ksqgtlctx(): *** OD-00011F49-00000000-00000000-00000000 mode=6 flags=0x10401 why=269 timeout=0 ***
2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00011F45-00000000-00000000-00000000 mode=2 flags=0x401 why=167 timeout=0 ***
2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-0000004B-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***
2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00000013-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***
2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TX-00020012-000002CE-00000000-00000000 mode=6 flags=0x401 why=170 timeout=0 ***
2017-03-21 07:12:21.094*:ksq.c@9033:ksqgtlctx(): *** TM-00000012-00000000-00000000-00000000 mode=3 flags=0x401 why=167 timeout=21474836 ***
2017-03-21 07:12:21.095*:ksq.c@9033:ksqgtlctx(): *** TX-00050012-00000300-00000000-00000000 mode=6 flags=0x401 why=170 timeout=0 ***
2017-03-21 07:12:21.095*:ksq.c@9033:ksqgtlctx(): *** TX-000A0011-0000031C-00000000-00000000 mode=4 flags=0x10001 why=171 timeout=21474836 ***
提交会话1
SQL> commit;
Commit complete.
会话2马上 DROP 成功。
SQL> drop index ind_drop_index_1 online;
Index dropped.
