如何在生产数据库中创建一个追踪客户端IP地址的触发器

一个例子：

$ ifconfig -a
lo0: flags=1000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000
eri0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
        inet 172.28.65.15 netmask ffffff00 broadcast 172.28.65.255

$ tnsping orcl

TNS Ping Utility for Solaris: Version 9.2.0.4.0 - Production on 02-AUG-2005 14:03:20

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.65.16)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (10 msec)
$ sqlplus wacos/oss@orcl

SQL> select sys_context('userenv', 'ip_address') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
172.28.65.15

创建一个追踪客户端IP地址的触发器：

SQL>create or replace trigger on_logon_trigger

 after logon on database

 begin

dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); 

end;

 / 

SQL>exit

$sqlplus wacos/oss@orcl

SQL> select client_info from v$session;

CLIENT_INFO
----------------------------------------------------------------
172.28.65.15

1 rows selected.

这样我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。