本文转自:http://www.zhongweicheng.com/?p=1786
使用sys创建一个表,用于记录客户端登录数据库服务器的信息
[oracle@vzwc1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 17:53:35 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create table login_history
2 (username varchar2(60),
3 machine varchar2(60),
4 module varchar2(60),
5 login_time date,
6 ip varchar2(50)
7 );
Table created.
使用sys创建获取session登录信息的触发器
SQL> create or replace trigger login_log
2 after logon on database
3 begin
4 insert into sys.login_history
5 select username,machine,module,sysdate,sys_context('userenv','ip_address')
6 from gv$session where audsid = userenv('sessionid');
7 end;
8 /
Trigger created.
用客户端登录oracle,然后查询sys.login_history
SQL> select * from sys.login_history where ip is not null;
USERNAME MACHINE MODULE LOGIN_TIME IP
——– —————— —————————- ———— ——————
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
SYSTEM WORKGROUP\ZWC-PC Toad.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC plsqldev.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
ZWC WORKGROUP\ZWC-PC sqlplus.exe 28-MAR-15 192.168.1.100
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53
SYSTEM vzwc3 sqlplus@vzwc3 (TNS V1-V3) 28-MAR-15 192.168.1.53