Oracle用户登录信息的统计

Oracle用户登录信息的统计

1.创建存储Oracle用户登录信息的表：

create table stats$user_log
(
user_id VARCHAR2(30),
session_id NUMBER(10),
host_name VARCHAR2(30),
last_module VARCHAR2(30),
logon_day DATE,
logon_time VARCHAR2(15),
logoff_day DATE,
logoff_time VARCHAR2(10),
elapsed_minutes NUMBER(10)
)

2.创建用户登录之后的触发器统计用户登陆时的信息：

create or replace trigger logon_trigger
after logon on database
begin
insert into stats$user_log
values(user,
sys_context('userenv','sessionid'),
sys_context('userenv','host'),
null,
sysdate,
to_char(sysdate,'hh24:mi:ss'),
null,
null,
null
);
end;

3.创建用户登出之前的触发器统计用户登出时的信息：

create or replace trigger logoff_trigger
before logoff on database
begin
update stats$user_log
set last_module =
(select module
from v$session
where sys_context('USERENV', 'SESSIONID') = audsid)
where sys_context('USERENV', 'SESSIONID') = session_id;

update stats$user_log
set logoff_day = sysdate
where sys_context('USERENV', 'SESSIONID') = session_id;

update stats$user_log
set logoff_time = to_char(sysdate, 'hh24:mi:ss')
where sys_context('USERENV', 'SESSIONID') = session_id;

update stats$user_log
set elapsed_minutes = round((logoff_day - logon_day) * 1440)
where sys_context('USERENV', 'SESSIONID') = session_id;
end;

4.查看用户登入登出信息：