ORA-04098错误的文档说明如下:
ORA-04098: trigger 'string.string' is invalid and failed re-validation
Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
如果触发器可以disable或drop,那么disable或drop掉就可以了,但是如果不能disableh和drop那么常规方法不就能处理这个错误,需要使用隐藏参数。
测试如下:
创建序列
create sequence seq minvalue 0 maxvalue 999;
创建触发器
create or replace trigger test_trigger
after ddl on database
declare seqnum number;
begin
select seq.nextval into seqnum from dual;
end;
create or replace trigger test_trigger1
before ddl on database
declare seqnum number;
begin
select seq.nextval into seqnum from dual;
end;
删除序列使触发器失效 drop sequence seq;
删除时就会触发ORA-04098错误但是序列还是能删掉。之后执行ddl就会失败
SQL> create table test1(id int);
create table test1(id int)
ORA-04098: 触发器 'CHENJP.TEST_TRIGGER' 无效且未通过重新验证
disable或drop触发器
SQL> alter trigger CHENJP.TEST_TRIGGER disable;
alter trigger CHENJP.TEST_TRIGGER disable
ORA-04098: 触发器 'CHENJP.TEST_TRIGGER1' 无效且未通过重新验证
SQL> alter trigger CHENJP.TEST_TRIGGER1 disable;
alter trigger CHENJP.TEST_TRIGGER1 disable
ORA-04098: 触发器 'CHENJP.TEST_TRIGGER' 无效且未通过重新验证
SQL> drop trigger CHENJP.TEST_TRIGGER ;
drop trigger CHENJP.TEST_TRIGGER
ORA-04098: 触发器 'CHENJP.TEST_TRIGGER1' 无效且未通过重新验证
SQL> drop trigger CHENJP.TEST_TRIGGER1;
drop trigger CHENJP.TEST_TRIGGER1
ORA-04098: 触发器 'CHENJP.TEST_TRIGGER' 无效且未通过重新验证
这时需要使用隐藏参数_system_trig_enabled禁用触发器,然后disable或drop触发器,操作如下:
SQL> alter system set "_system_trig_enabled"=false;
System altered.
SQL> drop trigger CHENJP.TEST_TRIGGER ;
Trigger dropped
SQL> drop trigger CHENJP.TEST_TRIGGER1;
Trigger dropped
SQL> alter system set "_system_trig_enabled"=true;
System altered.