ORA-00604,ORA-01438,ORA-06512 --trigger
--开发表示:连不上数据库报错如下
---告警日志报错
Errors in file /u01/app/oracle/diag/rdbms/otestdb/otestdb/trace/otestdb_ora_113591.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 2
---trace 日志
[oracle@testdb ~]$ vi /u01/app/oracle/diag/rdbms/otestdb/otestdb/trace/otestdb_ora_177585.trc
Trace file /u01/app/oracle/diag/rdbms/otestdb/otestdb/trace/otestdb_ora_177585.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2/db_1
System name: Linux
Node name: testdb
Release: 3.10.0-327.el7.x86_64
Version: #1 SMP Fri Nov 20 00:18:34 PST 2015
Machine: x86_64
Instance name: otestdb
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 177585, image: oracle@testdb
*** 2020-04-21 13:58:45.297
*** SESSION ID:(4163.65453) 2020-04-21 13:58:45.297
*** CLIENT ID:() 2020-04-21 13:58:45.297
*** SERVICE NAME:(otestdb) 2020-04-21 13:58:45.297
*** MODULE NAME:(SQL Developer) 2020-04-21 13:58:45.297
*** ACTION NAME:() 2020-04-21 13:58:45.297
Skipped error 604 during the execution of SYS.LOGIN_ON_INFO
*** 2020-04-21 13:58:45.297
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
----- Error Stack Dump -----
ORA-00604: µÝ¹é SQL ¼¶±ð 1 ³öÏÖ´íÎó
ORA-01438: Öµ´óÓÚΪ´ËÁÐÖ¸¶¨µÄÔÊÐí¾«¶È
ORA-06512: ÔÚ line 2
[oracle@testdb ~]$ sqlplus / as sysdba
---报错触发器
SYS.LOGIN_ON_INFO
--查询触发器body内容
select trigger_body from dba_triggers where trigger_name='LOGIN_ON_INFO';
BEGIN
INSERT into LOGINFO_LOG(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
SELECT AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
FROM v$session WHERE AUDSID=USERENV('SESSIONID');
END;
SELECT DBMS_METADATA.get_ddl('TRIGGER','LOGIN_ON_INFO') FROM dual;
14:03:50 SYS@otestdb(testdb)> SELECT DBMS_METADATA.get_ddl('TRIGGER','LOGIN_ON_INFO') FROM dual;
DBMS_METADATA.GET_DDL('TRIGGER','LOGIN_ON_INFO')
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "SYS"."LOGIN_ON_INFO"
AFTER LOGON
ON DATABASE
BEGI
14:03:56 SYS@otestdb(testdb)>
---具体详细信息
begin
dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;
BEGIN
INSERT into LOGINFO_LOG(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
SELECT AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
FROM v$session WHERE AUDSID=USERENV('SESSIONID');
END;
-------
--直接禁用触发器,由于监听开着导致触发器禁用不了
ALTER TRIGGER SYS.LOGIN_ON_INFO [DISABLE | ENABLE ];
--最后 关闭监听 删掉触发器
drop trigger SYS.LOGIN_ON_INFO ;
---参见MOS文档:1275094.1
column TRIGGER_BODY format a1000
set pagesize 1000
spool trg.txt
select
-- OWNER,
-- TRIGGER_NAME,
TRIGGER_TYPE, TRIGGERING_EVENT, STATUS, TRIGGER_BODY
from DBA_TRIGGERS
where trim(triggering_event) = 'LOGIN_ON_INFO';
spool off
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了