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