由查找session ip 展开---函数、触发器、包
一、userenv函数、sys_context函数--查看当前客户端会话的session ip信息
sql>select sys_context('userenv','ip_address') from dual;
sys_context('userenv','ip_address')
--------------------------------------------------------------------------------
192.168.56.117
展开：可以通过userenv函数或者sys_context函数可以获得当前会话的信息
sql>select userenv('language') from dual;
userenv('language')
----------------------------------------------------
american_america.zhs16gbk
--使用参数列举
--isdba：返回当前用户是否是dba，如果是则返回true
--session：返回当前会话的标志
--extryid：返回会话入口标志
--instance：返回当前instance的标志
--language：返回当前环境语言变量
--lang：返回当前环境的语言缩写
--terminal：返回用户的终端或机器的标志
sql>select sys_context('userenv','language') from dual;
sys_context('userenv','language')
------------------------------------------------------------------------------------------------
american_america.zhs16gbk
sql>select sys_context('userenv','host') from dual;
sys_context('userenv','host')
--------------------------------------------------------------------------------
workgroup\fpa4gfvzxulbfcr
二、v$session视图中通过v$session视图，将客户端ip信息存入client_identifier字段或client_info字段
使用client_info字段
sql>execdbms_application_info.set_client_info(sys_context('userenv','ip_address'));
sql>select username,sid,serial#,client_info,client_identifier from v$session wheresid=(select sys_context('userenv','sid') from dual);
使用client_identifier字段
sql>exec dbms_session.set_identifier(sys_context('userenv','ip_address'));
sql>select username,sid,serial#,client_info,client_identifier from v$session wheresid=(select sys_context('userenv','sid') from dual);
范例截图如下：
三、触发器通过触发器调用，新的客户端连接开启时自动触发，将ip信息写入client_info字段
create or replace triggeron_login_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
/
通过触发器调用，新的客户端连接开启时自动触发，将ip信息写入client_identifier字段
create or replace triggeron_login_trigger
after logon on database
begin
dbms_session.set_identifier(sys_context('userenv','ip_address'));
end;
/
四、使用utl_inaddr package在没有触发器记录的前提下，通过utl_inaddr package来实现获取session ip，而且可以获取其它session ip。
通过说明utl_inaddr package的工作原理，体现出utl_inaddr package是如何实现的。
实验分析开始：
[root@org54~]# ps -ef |grep sql
oracle 2740 2707 0 21:17 pts/1 00:00:00 sqlplus
oracle 3479 3451 0 21:49 pts/2 00:00:00 sqlplus
root 3482 3375 0 21:49 pts/3 00:00:00 grep sql
[root@org54~]# su - oracle
[oracle@org54~]$ ps -ef |grep lo
oracle 2770 2740 0 21:18 ? 00:00:00 oraclemetro (description=(local=yes)(address=(protocol=beq)))
oracle 3480 3479 0 21:49 ? 00:00:00 oraclemetro(description=(local=yes)(address=(protocol=beq)))
oracle 3520 3488 0 21:50 pts/3 00:00:00 grep lo
补充：安装strace工具，跟踪进程执行时的系统调用和所接收的信号。
——引用自网络，strace注解：在linux世界，进程不能直接访问硬件设备，当进程需要访问硬件设备(比如读取磁盘文件，接收网络数据等等)时，必须由用户态模式切换至内核态模式，通过系统调用访问硬件设备。strace可以跟踪到一个进程产生的系统调用,包括参数，返回值，执行消耗的时间。
——引用结束。
[root@org54rhel_5.5 x86_64 dvd]# cd server/ --使用linux光盘安装工具
[root@org54server]# ls -ll |grep strace
-r--r--r-- 326root root 175066 jan 18 2010 strace-4.5.18-5.el5_4.1.i386.rpm
[root@org54server]# rpm -ivh strace-4.5.18-5.el5_4.1.i386.rpm
warning:strace-4.5.18-5.el5_4.1.i386.rpm: header v3 dsa signature: nokey, key id37017186
preparing... ########################################### [100%]
1:strace ###########################################[100%]
[root@org54server]# rpm -qa |grep strace
strace-4.5.18-5.el5_4.1
补充完毕
[oracle@org54~]$ strace -p 2770 --打开跟踪
sql>select utl_inaddr.get_host_address('org54') from dual; --执行查询
utl_inaddr.get_host_address('org54')
--------------------------------------------------------------------------------------------------------
192.168.56.5
到strace跟踪信息界面下，查看信息如下：
process 2770attached - interrupt to quit
read(8,\0\323\0\0\6\0\0\0\0\0\3^!a\200\0\0\0\0\0\0tl\351\tj\0\0\0|\360\345...,2064) = 211
gettimeofday({1404438819,340059}, null) = 0
gettimeofday({1404438819,340388}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
times(null) = 429805698
gettimeofday({1404438819,343035}, null) = 0
gettimeofday({1404438819,343625}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
gettimeofday({1404438819,344254}, null) = 0
times(null) = 429805698
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
times(null) = 429805698
gettimeofday({1404438819,345897}, null) = 0
gettimeofday({1404438819,346375}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
gettimeofday({1404438819,348358}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
gettimeofday({1404438819,349038}, null) = 0
gettimeofday({1404438819,349493}, null) = 0
gettimeofday({1404438819,349742}, null) = 0
gettimeofday({1404438819,350092}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
gettimeofday({1404438819,350832}, null) = 0
gettimeofday({1404438819,351126}, null) = 0
gettimeofday({1404438819,351435}, null) = 0
gettimeofday({1404438819,351851}, null) = 0
gettimeofday({1404438819,352268}, null) = 0
getrusage(rusage_self,{ru_utime={0, 440932}, ru_stime={0, 423935}, ...}) = 0
gettimeofday({1404438819,353079}, null) = 0
gettimeofday({1404438819,353494}, null) = 0
open(/etc/hosts,o_rdonly) = 24
--注意此条语句，表示当我们进行utl_inaddr.get_host_address查询时，后台进程会去读取hosts文件，如果存在解析关系，便会返回信息显示，以下会验证如果没有解析的现象
fcntl64(24,f_getfd) = 0
fcntl64(24,f_setfd, fd_cloexec) = 0
fstat64(24,{st_mode=s_ifreg|0644, st_size=416, ...}) = 0
mmap2(null,4096, prot_read|prot_write, map_private|map_anonymous, -1, 0) = 0x11a000
read(24,# do not remove the following li..., 4096) = 416
close(24) = 0
munmap(0x11a000,4096) = 0
gettimeofday({1404438819,359617}, null) = 0
gettimeofday({1404438819,359908}, null) = 0
getrusage(rusage_self,{ru_utime={0, 441932}, ru_stime={0, 424935}, ...}) = 0
gettimeofday({1404438819,360851}, null) = 0
gettimeofday({1404438819,363074}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805700
gettimeofday({1404438819,366127}, null) = 0
write(11,\1q\0\0\6\0\0\0\0\0\20\27\30\252g\312n\23\337\326\212\21+%r>\367|xr\7\3...,337) = 337
read(8,\0\25\0\0\6\0\0\0\0\0\3\5\\21\0\0\0\17\0\0\0, 2064) = 21
gettimeofday({1404438819,369259}, null) = 0
gettimeofday({1404438819,369906}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805701
gettimeofday({1404438819,370962}, null) = 0
gettimeofday({1404438819,371394}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
gettimeofday({1404438819,372304}, null) = 0
gettimeofday({1404438819,372741}, null) = 0
gettimeofday({1404438819,373117}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805701
gettimeofday({1404438819,375003}, null) = 0
gettimeofday({1404438819,375561}, null) = 0
gettimeofday({1404438819,376567}, null) = 0
write(11,\0\204\0\0\6\0\0\0\0\0\4\1\0\0\0\37\0\1\1\0\0\0{\5\0\0\0\0\21\0\0\0...,132) = 132
read(8,\0\34\0\0\6\0\0\0\0\0\21i#l,\351\t\1\0\0\0\21\0\0\0\3\223$, 2064) =28
gettimeofday({1404438819,378886}, null) = 0
gettimeofday({1404438819,379120}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805702
gettimeofday({1404438819,380076}, null) = 0
gettimeofday({1404438819,380433}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805702
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805702
gettimeofday({1404438819,384734}, null) = 0
gettimeofday({1404438819,385145}, null) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
getrusage(rusage_self,{ru_utime={0, 442932}, ru_stime={0, 424935}, ...}) = 0
times(null) = 429805703
gettimeofday({1404438819,386442}, null) = 0
gettimeofday({1404438819,386802}, null) = 0
gettimeofday({1404438819,387042}, null) = 0
write(11,\0\21\0\0\6\0\0\0\0\0\t\1\0\0\0!\0, 17) = 17
read(8,
*缺少hosts解析时，utl_inaddr.get_host_address查询现象
[root@org54~]# vi /etc/hosts --将客户端fpa4gfvzxulbfcr解析地址注释掉
# do notremove the following line, or various programs
# that requirenetwork functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.56.11rac11
192.168.56.22rac22
192.168.56.31rac11-vip
192.168.56.32rac22-vip
192.168.2.11rac11-priv
192.168.2.22rac22-priv
192.168.56.7node1
192.168.56.8gc1
192.168.56.5 org54
#192.168.56.117fpa4gfvzxulbfcr
~
~
/etc/hosts19l, 417c written
客户端登陆
主机端：
sql>col machine for a50
sql>select username,machine,program,sql_id from v$session where username is notnull
--查询到登陆主机
username machine program sql_id
------------------------------------------------------------- -------------
sys org54 sqlplus@org54 (tns v1-v3)
scott workgroup\fpa4gfvzxulbfcr sqlplus.exe
sys org54 sqlplus@org54 (tns v1-v3) 8w8k8ss45hm25
sql>select utl_inaddr.get_host_address('fpa4gfvzxulbfcr') from dual; --在没有hosts解析的情况下，报错了
selectutl_inaddr.get_host_address('fpa4gfvzxulbfcr') from dual
*
error at line1:
ora-29257:host fpa4gfvzxulbfcr unknown
ora-06512: atsys.utl_inaddr, line 19
ora-06512: atsys.utl_inaddr, line 40
ora-06512: atline 1
[root@org54~]# vi /etc/hosts --将客户端fpa4gfvzxulbfcr解析地址重新添加进去
# do notremove the following line, or various programs
# that requirenetwork functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.56.11rac11
192.168.56.22rac22
192.168.56.31rac11-vip
192.168.56.32rac22-vip
192.168.2.11rac11-priv
192.168.2.22rac22-priv
192.168.56.7node1
192.168.56.8gc1
192.168.56.5 org54
192.168.56.117fpa4gfvzxulbfcr
~
~
/etc/hosts19l, 417c written
sql>select utl_inaddr.get_host_address('fpa4gfvzxulbfcr') from dual; --再次发起查询
utl_inaddr.get_host_address('fpa4gfvzxulbfcr')
--------------------------------------------------------------------------------------------------------
192.168.56.117
小结：
综上可知，使用utl_inaddr package在捕获session ip时不再依赖数据库的信息。在触发器没有记录的情况下，可以实现捕捉其它session的地址信息。
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
*****************************************************************************************************