自動把登錄的用戶信息記錄到一個表中

[lula]

Oracle數據庫有很多個用戶﹐現在要實現有用戶登錄時自動把用戶名及登錄時間退出時間等信息寫了一個表中﹐表為logininfo(user_id,login_time,logout_time),怎么實現此功能﹖

[tgm78]

lz写个trigger即可

给你一个现成的例子，稍做改动即可使用

create or replace trigger tr_user_logon after logon on database
declare
v_username varchar2(30);
v_machine varchar2(64);
v_osuser varchar2(30);
v_logon_time date;
v_cnt number;
v_sess_cnt number;
v_proc_cnt number;
cursor cur_session is
select username, machine, osuser, logon_time
from v$session
where audsid = userenv('SESSIONID')
and username is not null
and username not in ('GBSMAN', 'GBSMGR', 'ACCTMAN', 'PDMAN', 'GBSPKG',
'GACPKG', 'GBSMIG', 'GBSTRAN', 'GBSDEBUG')
and username in
(select ltrim(rtrim(upper(user_name))) from gusermgr.gbs_user_tbl);
v_open_mode varchar2(20);
begin
if user<>'DGEXP' then

select count(*)
into v_cnt
from v$session
where audsid = userenv('SESSIONID')
and username is not null
and username not in ('SYS', 'SYSTEM', 'DBMGR');
if v_cnt > 0 then
select value_num
into v_proc_cnt
from gbs_parameter
where name = 'processes';
select count(*) into v_sess_cnt from v$session;
if v_sess_cnt >= v_proc_cnt then
raise_application_error(-20001,
'maximum number of processes (' ||
v_proc_cnt || ') exceeded');
end if;
end if;
open cur_session;
loop
fetch cur_session
into v_username, v_machine, v_osuser, v_logon_time;
exit when cur_session%NOTFOUND;
v_machine := rtrim(v_machine, chr(0));
insert into mon_logon_info
values
(v_username, v_machine, v_osuser, v_logon_time);
end loop;
close cur_session;
end if;
end;

[CH_MARS]

蹭分了

[adam0730]

好长,加点注释嘛,这是个好习惯