DDL觸發器設定導致DDL無法執行（一）

打算進行個測試，結果發現建表時報錯：

SQL> CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))
   2 PARTITION BY RANGE (CREATED) SUBPARTITION BY LIST (TYPE)
   3 (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1', 'YYYY-MM'))
   4 (SUBPARTITION P1SP1 VALUES ('TABLE'),
   5 SUBPARTITION P1SP2 VALUES ('INDEX'),
   6 SUBPARTITION P1SP3 VALUES ('VIEW'),
   7 SUBPARTITION P1SP4 VALUES ('SYNONYM'),
   8 SUBPARTITION P1SP5 VALUES (DEFAULT)),
   9 PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2', 'YYYY-MM'))
  10 (SUBPARTITION P2SP1 VALUES ('TABLE'),
  11 SUBPARTITION P2SP2 VALUES ('INDEX'),
  12 SUBPARTITION P2SP3 VALUES ('VIEW'),
  13 SUBPARTITION P2SP4 VALUES ('SYNONYM'),
  14 SUBPARTITION P2SP5 VALUES (DEFAULT)),
  15 PARTITION P3 VALUES LESS THAN (MAXVALUE)
  16 (SUBPARTITION P3SP1 VALUES ('TABLE'),
  17 SUBPARTITION P3SP2 VALUES ('INDEX'),
  18 SUBPARTITION P3SP3 VALUES ('VIEW'),
  19 SUBPARTITION P3SP4 VALUES ('SYNONYM'),
  20 SUBPARTITION P3SP5 VALUES (DEFAULT)));
CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object
EYGLE.BIN$trcEn8qthIjgQKjAEwAm+g==$0
ORA-06512: at line 24

SQL> CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))
   2 PARTITION BY RANGE (CREATED) SUBPARTITION BY LIST (TYPE)
   3 (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-1', 'YYYY-MM'))
   4 (SUBPARTITION P1SP1 VALUES ('TABLE'),
   5 SUBPARTITION P1SP2 VALUES ('INDEX'),
   6 SUBPARTITION P1SP3 VALUES ('VIEW'),
   7 SUBPARTITION P1SP4 VALUES ('SYNONYM'),
   8 SUBPARTITION P1SP5 VALUES (DEFAULT)),
   9 PARTITION P2 VALUES LESS THAN (TO_DATE('2012-2', 'YYYY-MM'))
  10 (SUBPARTITION P2SP1 VALUES ('TABLE'),
  11 SUBPARTITION P2SP2 VALUES ('INDEX'),
  12 SUBPARTITION P2SP3 VALUES ('VIEW'),
  13 SUBPARTITION P2SP4 VALUES ('SYNONYM'),
  14 SUBPARTITION P2SP5 VALUES (DEFAULT)),
  15 PARTITION P3 VALUES LESS THAN (MAXVALUE)
  16 (SUBPARTITION P3SP1 VALUES ('TABLE'),
  17 SUBPARTITION P3SP2 VALUES ('INDEX'),
  18 SUBPARTITION P3SP3 VALUES ('VIEW'),
  19 SUBPARTITION P3SP4 VALUES ('SYNONYM'),
  20 SUBPARTITION P3SP5 VALUES (DEFAULT)));
CREATE TABLE T_EXCHANGE (ID NUMBER, CREATED DATE, TYPE VARCHAR2(18))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 24

前後兩次的報錯資訊還不一樣，而且二者包含的資訊都有意義。從第一次執行可以看出，執行DDL操作引發了ORA-4020死鎖，而第二次則表示導致錯誤出現的因素和DDL觸發器有關。

由於是測試環境，部署的環境比較複雜，很可能是其他元件或者某些測試程式碼導致DDL觸發器出現錯誤。

檢查了一下發生死鎖時報錯物件，這是回收站中的一個物件：

SQL> select owner, object_name, original_name, operation, type 
  2  from dba_recyclebin
  3  where object_name = 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0';

OWNER OBJECT_NAME                    ORIGINAL_NAME OPERATION TYPE
----- ------------------------------ ------------- --------- -----
EYGLE BIN$trcI7ykLAu7gQKjAEwAnkA==$0 T_PWD         DROP      TABLE

SQL> select * from dba_dependencies where type = 'TRIGGER' and REFERENCED_NAME in ('T_PWD', 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0');

no rows selected

SQL> select * from dba_dependencies where  REFERENCED_NAME in ('T_PWD', 'BIN$trcI7ykLAu7gQKjAEwAnkA==$0');

no rows selected

系統中沒有任何物件依賴於回收站中的這個物件，甚至沒有任何物件依賴這個回收站物件刪除前的原始物件。

SQL> SELECT OWNER, TRIGGERING_EVENT, COUNT(*) FROM DBA_TRIGGERS GROUP BY OWNER, TRIGGERING_EVENT ORDER BY 1;

OWNER                          TRIGGERING_EVENT                           COUNT(*)
------------------------------ ---------------------------------------- ----------
DBFW_CONSOLE_ACCESS            DDL                                               1
DBFW_CONSOLE_ACCESS            LOGOFF                                            1
DBFW_CONSOLE_ACCESS            LOGON                                             1
EXFSYS                         ALTER OR RENAME                                   1
EXFSYS                         CREATE OR ALTER                                   1
EXFSYS                         DROP                                              2
EXFSYS                         TRUNCATE                                          1
MDSYS                          CREATE                                            1
MDSYS                          DELETE                                            8
MDSYS                          DROP                                              7
MDSYS                          INSERT                                            9
MDSYS                          INSERT OR UPDATE                                  3
MDSYS                          INSERT OR UPDATE OR DELETE                        3
MDSYS                          TRUNCATE                                          1
MDSYS                          UPDATE                                            6
OLAPSYS                        DELETE                                            8
OLAPSYS                        INSERT OR UPDATE                                 40
SYS                            ALTER                                             1
SYS                            CREATE                                            2
SYS                            DROP                                              2
SYS                            SHUTDOWN                                          2
SYS                            STARTUP                                           2
SYSMAN                         DELETE                                           16
SYSMAN                         INSERT                                           18
SYSMAN                         INSERT OR UPDATE                                  6
SYSMAN                         INSERT OR UPDATE OR DELETE                        1
SYSMAN                         UPDATE                                            6
SYSMAN                         UPDATE OR DELETE                                  1
SYSTEM                         INSERT                                            1
SYSTEM                         UPDATE OR DELETE                                  1
TEST                           INSERT OR UPDATE OR DELETE                        1
WMSYS                          CREATE OR ALTER OR DROP OR RENAME                 1
WMSYS                          DROP                                              1
XDB                            DROP OR TRUNCATE                                  1
XDB                            INSERT OR UPDATE                                  1
XDB                            INSERT OR UPDATE OR DELETE                        2
XDB                            UPDATE OR DELETE                                  8

37 rows selected.

系統中只有一個DDL觸發器，內容如下：

SQL> select trigger_body from dba_triggers
   2 where trigger_name = 'TRIGGER_LOGIN';

TRIGGER_BODY
--------------------------------------------------------------------------------
begin
if dbfw_console_access.is_local then
insert into dbfw_console_access.event(id,username,sessionid,event,text)
select dbfw_console_access.event_seq.nextval,
sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','SESSIONID'),
'LOGIN',
null
from dual;
end if;
end;

有意思的時，回收站中報錯的表是Eygle測試密碼的臨時表，使用完畢後被他刪除。而這個觸發器是Kamus測試FireWall功能建立的。而當我執行DDL時，兩個完全沒有關係的物件組合在一起報錯。

Eygle建立並刪除的表本身並沒有什麼特殊之處，而且已經在回收站中，就更不會對系統有什麼額外的影響。相比較，Kamus建立的觸發器就比較可疑了，畢竟這是一個DDL觸發器，在執行DDL語句時就會觸發，問題多半是這個觸發器導致的。但是這個觸發器實質上只有一個INSERT語句，沒有道理導致死鎖的產生，何況觸發器和回收站中的物件完全沒有任何聯絡。

簡單的禁用或刪除觸發器同樣會引發錯誤：

SQL> conn / as sysdba
Connected.
SQL> alter trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN disable;
alter trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN disable
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 24

SQL> drop trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN;
drop trigger DBFW_CONSOLE_ACCESS.TRIGGER_LOGIN
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 24

看來問題不像想象中的那麼簡單，必須找到問題的原因才可以徹底解決。