背景：

1. 為防止誤刪除數(shù)據(jù)，我們會(huì)在數(shù)據(jù)庫中創(chuàng)建trigger，禁止對(duì)表進(jìn)行drop或者truncate。
2. 通常給表創(chuàng)建索引時(shí)候，為了避免創(chuàng)建索引過程中鎖表引起性能問題，我們會(huì)要求創(chuàng)建索引時(shí)候加上online參數(shù)。

?
普通創(chuàng)建索引 和 online 方式創(chuàng)建索引的區(qū)別：

① 普通創(chuàng)建索引的命令create index會(huì)先鎖表，然后再創(chuàng)建索引，如果表中數(shù)據(jù)量很大，會(huì)造成阻塞DML語句；

② create index online 允許其它會(huì)話修改索引字段，但如果修改索引字段的會(huì)話沒有commit或是rollbak，則索引創(chuàng)建會(huì)被阻塞。

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

?
實(shí)驗(yàn)：

1. 創(chuàng)建trigger

-- 創(chuàng)建trigger，禁止對(duì)表進(jìn)行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. 創(chuàng)建測試表

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

-- 插入數(shù)據(jù)
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方式創(chuàng)建索引

-- 使用online 方式創(chuàng)建索引
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 

?結(jié)論：可以看到創(chuàng)建索引時(shí)，觸發(fā)了trigger_undroptable觸發(fā)器，創(chuàng)建報(bào)錯(cuò)。但是我們發(fā)現(xiàn)IDX1索引也創(chuàng)建好了，并且多出了SYS_JOURNAL_94129表和索引，狀態(tài)都是VALID。?

4. 校驗(yàn)索引

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.

?結(jié)論：可以看到SQL語句執(zhí)行計(jì)劃中，用到了索引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 報(bào)錯(cuò)，無法正常刪除。

-- 解決辦法：
-- 查詢object_id 94129 就是我們創(chuàng)建的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

注意: 這個(gè)索引是一個(gè)不完整的索引，重啟DB之后，索引自動(dòng)清除了，不見了！！即使不重啟DB ，smon進(jìn)程也會(huì)清除該索引，所以，我們還是需要用管理員用戶，或者先禁用trigger重新創(chuàng)建正常的索引。

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 查看創(chuàng)建索引過程

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 

結(jié)論：可以發(fā)現(xiàn)使用online參數(shù)建立索引要建立一個(gè)臨時(shí)SYS_JOURNAL_94130 IOT表，索引創(chuàng)建完成后再drop table時(shí)，觸發(fā)到trigger。

7. rebuild index online

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

Index altered.

-- oline方式rebuild 報(bào)錯(cuò)
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

結(jié)論：使用online 方式rebuild index 同樣也會(huì)觸發(fā)trigger報(bào)錯(cuò)。

?
綜上： 當(dāng)我們數(shù)據(jù)庫中存在禁止drop table trigger 時(shí)候，我們使用online 方式創(chuàng)建索引，需要先disable trigger 或者使用trigger 約束范圍以外的用戶進(jìn)行創(chuàng)建。