oracle查询用户连接记录,记录ORACLE用户登录信息

首先，建一个表如表名：LOGIN_LOG

-- Create table

create table LOGIN_LOG

(

SESSION_ID INTEGER not null,

LOGIN_ON_TIME DATE,

LOGIN_OFF_TIME DATE,

USER_IN_DB VARCHAR2(60),

MACHINE VARCHAR2(40),

IP_ADDRESS VARCHAR2(40),

RUN_PROGRAM VARCHAR2(40)

)

tablespace PDA_DATA

pctfree 10

pctused 40

initrans 1

maxtrans 255

storage

(

initial 64K

next 128K

minextents 1

maxextents unlimited

pctincrease 0

);

再创建两个触发器：login_on_info和login_off_info：

create or replace trigger login_on_info

after logon on database

Begin

insert into login_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;

create or replace trigger login_off_info

before logoff on database

Begin

update login_log set login_off_time = sysdate where session_id = USERENV('SESSIONID');

exception

when others then

null;

end;

编译成功后完成，对了，要给用户有select v$session 的权限。[@more@]