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

 

posted @   钱若梨花落  阅读(784)  评论(0编辑  收藏  举报
(评论功能已被禁用)
编辑推荐:
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示