online 方式创建索引触发trigger怎么办？

背景：

1. 为防止误删除数据，我们会在数据库中创建trigger，禁止对表进行drop或者truncate。
2. 通常给表创建索引时候，为了避免创建索引过程中锁表引起性能问题，我们会要求创建索引时候加上online参数。

 
普通创建索引 和 online 方式创建索引的区别：

① 普通创建索引的命令create index会先锁表，然后再创建索引，如果表中数据量很大，会造成阻塞DML语句；

② create index online 允许其它会话修改索引字段，但如果修改索引字段的会话没有commit或是rollbak，则索引创建会被阻塞。

但是加online字段有一些限制：

 
实验：

1. 创建trigger

-- 创建trigger，禁止对表进行drop和truncate操作
SQL> conn / as sysdba
Connected.

SQL> create or replace trigger trigger_undroptable
  BEFORE DROP or TRUNCATE ON DATABASE
  begin
  if ora_login_user not in ('SYS','SYSTEM') THEN
    if upper(dictionary_obj_type) ='TABLE' THEN
       Raise_application_error (-20001,'Please not do DROP or TRUNCATE Table,You will be Caught!!!');
    end if;
  end if;
  end;
  /

Trigger created.

2. 创建测试表

SQL> conn sxc/sxc
Connected.

SQL> create table t(a int primary key,b int,c int,d int);

Table created.

SQL> set line222
SQL> select index_name,table_name from user_indexes;

INDEX_NAME                                     TABLE_NAME
----------------------------------- ----------------------------------------
SYS_C0014935                                    T

-- 插入数据
SQL> BEGIN
  FOR X IN 1 .. 1000 LOOP
    INSERT INTO t VALUES (x,x+1,x+2,x+3);
    COMMIT;
    DBMS_LOCK.SLEEP(0.2);
  END LOOP;
END;
/

3. 使用online方式创建索引

-- 使用online 方式创建索引
SQL> create index idx1 on t (b) online;
create index idx1 on t (b) online
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4

SQL> select index_name,table_name,STATUS from user_indexes;

INDEX_NAME                                           TABLE_NAME                          STATUS
------------------------------------------- ------------------------------------------------------------
SYS_C0014935                                          T                                   VALID
IDX1                                                  T                                   VALID
SYS_IOT_TOP_94130                                     SYS_JOURNAL_94129                   VALID 

 结论：可以看到创建索引时，触发了trigger_undroptable触发器，创建报错。但是我们发现IDX1索引也创建好了，并且多出了SYS_JOURNAL_94129表和索引，状态都是VALID。 

4. 校验索引

SQL> set line222
SQL> set autotrace off          
SQL> alter session set statistics_level=all ;
Session altered.
SQL> VARIABLE id NUMBER
SQL> EXECUTE :id := 10;

PL/SQL procedure successfully completed.

SQL> SELECT count(*) FROM t WHERE b < :id;

  COUNT(*)
----------
         8

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d3f4gyz6wsjmn, child number 0
-------------------------------------
SELECT count(*) FROM t WHERE b < :id

Plan hash value: 3080277828

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| IDX1 |      1 |      8 |      8 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"<:ID)

Note
-----
   - dynamic sampling used for this statement (level=2)

23 rows selected.

 结论：可以看到SQL语句执行计划中，用到了索引IDX1 ，说明可以正常使用。 

5. 删除索引

SQL> drop index idx1;
drop index idx1
           *
ERROR at line 1:
ORA-08104: this index object 94129 is being online built or rebuilt

-- 删除index 报错，无法正常删除。

-- 解决办法：
-- 查询object_id 94129 就是我们创建的index
SQL> set line222
SQL> col owner for a10
SQL> col OBJECT_NAME for a10
SQL> select owner,object_name,object_id,object_type from dba_objects where object_id='94129';

OWNER      OBJECT_NAM  OBJECT_ID OBJECT_TYPE
---------- ---------- ---------- --------------------------------------
SXC        IDX1            94129 INDEX

[oracle@ora11204 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 3 15:32:19 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> declare
  2  done boolean;
  3  begin
  4     done:=dbms_repair.online_index_clean(94129);  ---OBJECT_ID
  5  end;
  6  /

PL/SQL procedure successfully completed.

-- 再次查询 idx1 没有了
SQL> select index_name,table_name from user_indexes;

INDEX_NAME                                            TABLE_NAME
------------------------------------------------------------ -----------------------------------------------
SYS_C0014935                                           T

注意: 这个索引是一个不完整的索引，重启DB之后，索引自动清除了，不见了！！即使不重启DB ，smon进程也会清除该索引，所以，我们还是需要用管理员用户，或者先禁用trigger重新创建正常的索引。

SQL> startup force
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             864027528 bytes
Database Buffers          197132288 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.

SQL> select index_name,table_name from user_indexes;

INDEX_NAME                                            TABLE_NAME
------------------------------------------------------------ -----------------------------------------------
SYS_C0014935                                           T

6. 通过10046 trace 查看创建索引过程

SQL> set autotrace off
SQL> alter session set statistics_level=typical;     
Session altered.
SQL> alter session set events '10046 trace name context  forever,level 12';
Session altered.

SQL> create index idx1 on t (b) online;
create index idx1 on t (b) online
                     *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4

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

Session altered.

SQL> select SPID  from v$process where addr=(select PADDR from v$session where sid=(select distinct sid from v$mystat));

SPID
------------------------------------------------
1820

-- 查看trace文件
[oracle@ora11204 trace]$ tkprof LHR11G_ora_1820.trc LHR11G_ora_1820.txt 

结论：可以发现使用online参数建立索引要建立一个临时SYS_JOURNAL_94130 IOT表，索引创建完成后再drop table时，触发到trigger。

7. rebuild index online

-- 普通方式rebuild 正常
SQL> alter index SYS_C0014935 rebuild;

Index altered.

-- oline方式rebuild 报错
SQL> alter index SYS_C0014935 rebuild online;
alter index SYS_C0014935 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4

结论：使用online 方式rebuild index 同样也会触发trigger报错。

 
综上： 当我们数据库中存在禁止drop table trigger 时候，我们使用online 方式创建索引，需要先disable trigger 或者使用trigger 约束范围以外的用户进行创建。