公司测试数据库发现执行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 |
看来问题不像想象中的那么简单，必须找到问题的原因才可以彻底解决。