|
数据库版本 平台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删除成功 至此问题解决 |
