oracle 建立触发器实现登录失败的时候记录日志信息

问题：用户反映oracle用户最近总是会被锁定，解锁后过一天继续被锁定。

解决思路：v$session并不会记录ip信息，建立触发器实现登录失败的时候记录日志信息

    

CREATE OR REPLACE TRIGGER logon_denied_to_alert
 
AFTER servererror ON DATABASE
 
DECLARE
 
message VARCHAR2(168);
 
ip VARCHAR2(15);
 
v_os_user VARCHAR2(80);
 
v_module VARCHAR2(50);
 
v_action VARCHAR2(50);
 
v_pid VARCHAR2(10);
 
v_sid NUMBER;
 
v_program VARCHAR2(48);
 
BEGIN
 
IF(ora_is_servererror(1017)) THEN
 
-- get ip FOR remote connections :
 
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
 
ip := sys_context('userenv', 'ip_address');
 
END IF;
 
 
 
SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
 
SELECT p.spid, v.program
 
INTO v_pid, v_program
 
FROM v$process p, v$session v
 
WHERE p.addr = v.paddr
 
AND v.sid = v_sid;
 
 
 
v_os_user := sys_context('userenv', 'os_user');
 
dbms_application_info.read_module(v_module, v_action);
message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
 
' logon denied from ' || nvl(ip,'localhost') || ' ' ||
 
v_pid || ' ' || v_os_user || 'with ' || v_program || ' – ' ||
 
v_module || ' ' || v_action;
 
sys.dbms_system.ksdwrt(2, message);
END IF;
END;
/

参考&感谢https://www.bbsmax.com/A/Ae5RO8NmdQ/