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.查看用户登入登出信息：