Oracle使用触发器记录登录错误信息到文件

创建触发器记录登录失败用户信息，生成的alert_数据库名.log文件在trace文件夹下。

CREATE OR REPLACE TRIGGER logon_denied_to_alert
    AFTER SERVERERROR
    ON DATABASE
DECLARE
    MESSAGE       VARCHAR2 (256);
    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);
    v_client_id   VARCHAR2 (64);
BEGIN
    IF (ora_is_servererror (1017))
    THEN
        -- get IP for remote connections:
        IF SYS_CONTEXT ('userenv', 'network_protocol') = 'tcp'
        THEN
            ip := SYS_CONTEXT ('userenv', 'ip_address');
        END IF;

        SELECT DISTINCT sid
        INTO v_sid
        FROM sys.v_$mystat;

        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);

        v_client_id := SYS_CONTEXT ('userenv', 'client_identifier');

        MESSAGE :=
               TO_CHAR (SYSDATE, 'Dy Mon dd HH24:MI:SS YYYY')
            || ' logon denied '
            || 'IP ='
            || NVL (ip, 'localhost')
            || ' pid = '
            || v_pid
            || ' os user = '
            || v_os_user
            || ' client id = '
            || v_client_id
            || ' with program= '
            || v_program
            || ' module ='
            || v_module
            || ' action='
            || v_action;

        sys.DBMS_SYSTEM.ksdwrt (2, MESSAGE);
    -- remove comments from next line to let it hang for 5 minutes
    -- to be able to do more diagnostics on the operating system:
    -- sys.dbms_lock.sleep(300);
    END IF;
END;
/