通过触发器记录LOGON,LOGOUT

##创建日志记录表

logtime date,
action varchar2(30),
SID NUMBER,
username varchar2(30),
HOST varchar2(64),
OS_USER varchar2(64),
ipadr varchar2(30),
module varchar2(64),
terminal varchar2(64)
) tablespace users
partition by RANGE(logtime) INTERVAL (NUMTOYMINTERVAL(1,'month'))
(
PARTITION PART_202001  VALUES LESS THAN (TO_DATE ('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
);

创建logon触发器

after  logon on database         
begin
IF SYS_CONTEXT('USERENV','SESSION_USER') NOT IN ('SYS') 
THEN
insert into sys.logon_logs(logtime,action,sid,username,HOST,OS_USER,ipadr,module,terminal) values(
sysdate,
'LOGON',
sys_context('USERENV','SID'),
user,
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','MODULE'),
sys_context('USERENV','TERMINAL'));
commit;
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
end;
/

创建logout触发器

before logoff on database
begin
IF SYS_CONTEXT('USERENV','SESSION_USER') NOT IN ('SYS')
THEN
insert into sys.logon_logs(logtime,action,sid,username,HOST,OS_USER,ipadr,module,terminal) values(
sysdate,
'LOGOUT',
sys_context('USERENV','SID'),
user,
sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','MODULE'),
sys_context('USERENV','TERMINAL'));
commit;
END IF;
exception
WHEN OTHERS THEN
null;
end;
/