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 TIGGER 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