oracle通过sid远程登录,【DB笔试面试235】在Oracle中，怎么捕获用户登录信息，如SID，IP地址等？...

CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD

AFTER LOGON ON DATABASE

DECLARE

MIUSERSID NUMBER;

MTSESSION V$SESSION%ROWTYPE;

CURSOR CSESSION(IIUSERSID IN NUMBER) IS

SELECT * FROM V$SESSION

WHERE SID=IIUSERSID;

BEGIN

SELECT SID INTO MIUSERSID FROM V$MYSTAT WHERE ROWNUM<=1;

OPEN CSESSION(MIUSERSID);

FETCH CSESSION INTO MTSESSION;

--IF USER EXISTS THEN INSERT DATA

IF CSESSION%FOUND THEN

INSERT INTO LOG$INFORMATION(LOGIN_USER,LOGIN_TIME,IP_ADRESS,AUSID,TERMINAL,

OSUSER,MACHINE,PROGRAM,SID,SERIAL#)

VALUES(ORA_LOGIN_USER,SYSDATE,SYS_CONTEXT ('USERENV','IP_ADDRESS'),

USERENV('SESSIONID'),

MTSESSION.TERMINAL,MTSESSION.OSUSER,

MTSESSION.MACHINE,MTSESSION.PROGRAM,

MTSESSION.SID,MTSESSION.SERIAL#);

ELSE

--IF USER DON'T EXISTS THEN RETURN ERROR

SP_WRITE_LOG('SESSION INFORMATION ERROR:'||SQLERRM);

CLOSE CSESSION;

RAISE_APPLICATION_ERROR(-20099,'LOGIN EXCEPTION',FALSE);

END IF;

CLOSE CSESSION;

EXCEPTION

WHEN OTHERS THEN

SP_WRITE_LOG('LOGIN TRIGGER ERROR:'||SQLERRM);

END TR_LOGIN_RECORD;