ORA-04098錯誤解決方法

　　數據庫版本
平台SOLARIS

　　背景
用戶建立了一個TRIGGER:
create or replace trigger ddl_deny
before create or alter or drop on database
declare
begin
insert into ddl_logs values(ora_dict_obj_ownerora_dict_obj_namesysdate);
exception
when no_data_found then
null;
end;
目的大概就是記錄下所有的DDL操作但TRIGGER建立有錯誤發現:
:: alter>system@ORA>alter trigger ddl_deny disable;
alter trigger ddl_deny disable
*
ERROR 位於第 行:
ORA: 觸發器 DDL_DENY 無效且未通過重新驗證
:: drop>system@ORA>drop trigger ddl_deny;
drop trigger ddl_deny
*
ERROR 位於第 行:
ORA: 觸發器 DDL_DENY 無效且未通過重新驗證

　　此時觸發器不能編譯過去也不能刪除了因為觸發器本身裡面定義了DDL操作的觸發產生ORA: 觸發器 DDL_DENY 無效且未通過重新驗證

　　解決方法
首先查看用戶的權限是否正確
select owner object_name object_type status from dba_objects where object_name = <TRIGGER_NAME>;
:: select>system@ORA>select owner object_name object_type status from dba_o
bjects where object_name=DDL_DENY;
OWNER OBJECT_NAME OBJECT_TYPE STATUS

SYSTEM DDL_DENY TRIGGER INVALID
發現用戶權限沒有問題
接著設置診斷事件alter session set events= trace name errorstack level ;查看trace文件的內容如下
Dump file /db/app/oracle/admin/ora/udump/ora_ora_trc
Oraclei Enterprise Edition Release Production
With the Partitioning and Java options
PL/SQL Release Production
ORACLE_HOME = /db/app/oracle/product/
System name: SunOS
Node name: db
Release:
Version: Generic_
Machine: sunu
Instance name: ora
Redo thread mounted by this instance:
Oracle process number:
Unix process pid: image: oracle@db (TNS VV)
*** SESSION ID)
***
ksedmp: internal or fatal error
ORA: 觸發器DDL_DENY 無效且未通過重新驗證
Current SQL statement for this session:
alter trigger ddl_deny disable
Call Stack Trace
calling call entry argument values in hex
location type point (? means dubious value)

ksedmp()+ CALL ksedst()+ ? ? FFBEBC ?
FFBEADC ? FFBEADA ? ?
ksddoa()+ PTR_CALL ? ? ? FC ?
C ? ?
ksdpcg()+ CALL ksddoa()+ EBAC ? EC ? ?
C ? EBAC ? EB ?
ksdpec()+ CALL ksdpcg()+ ? FFBEBE ? EC ?
? ? ?
ksfpec()+ CALL ksdpec()+ ? A ? A ?
F ? ? FC ?
kgesev()+ PTR_CALL ? ? F ?
? ? ?
ksesec()+ CALL kgesev()+ ? ECA ? ?
? FFBEBA ? ?
kkttrex()+ CALL ksesec()+ ? ? ? ED ? ?
?
kktexeevt()+ CALL kkttrex()+ EA ? EB ?
FFBEBAE ? ?
EDC ? E ?
發現是內部嚴重錯誤其他看不出太多錯誤信息於是想到采用隱含參數_system_trigger_enabled=false在數據庫啟動的時候讓所有觸發器不起作用然後刪除數據庫的提示沒這個參數於是查詢了一下
:: select>system@ORA>select ksppinm from x$ksppi where substr(ksppinm)=_
and ksppinm like %tri% order by ksppinm;
KSPPINM

_cleanup_rollback_entries
_distributed_lock_timeout
_distributed_recovery_connection_hold_time
_number_cached_attributes
_system_trig_enabled
發現版本的參數是_system_trig_enabled於是讓用戶在初始化參數文件中設置此參數為false然後重啟數據庫刪除trigger刪除成功
至此問題解決

From:http://tw.wingwit.com/Article/program/Oracle/201311/17665.html