查看Oracle链接客户端的IP等信息

末蓝、 2022-06-01 09:52 1046阅读 0赞

查看当前链接ORACLE的IP

查看当前链接的IP(查看当前自己链接IP，不能查看其它的链接的IP)：

SELECT USER,SID,SERIAL\#,UTL\_INADDR.GET\_HOST\_ADDRESS AS HOST,SYS\_CONTEXT('USERENV','IP\_ADDRESS') AS LOCAL,SYSDATE FROM V$SESSION WHERE AUDSID=SYS\_CONTEXT('USERENV','SESSIONID');  
  
ORCL 214  1758 ::1  192.168.1.240 2018-01-26 10:52:29

查看所有链接Oracle的客户端的IP等信息：

step1: 查看每个oracle帐户的连接总数：

select username,count(username) from v$session where username is not null group by username;   
  
USERNAME  COUNT(USERNAME)  
ORCL 117  
SYS 1

step2: 缺省从 v$session 中不能直接获得客户端 IP，可以在数据库中创建一个追踪客户端IP地址的触发器：

create or replace trigger on\_logon\_trigger after logon on database    
begin    
    dbms\_application\_info.set\_client\_info(sys\_context('userenv', 'ip\_address'));    
end;     
/

step3: 比较常用的显示客户端信息的sql(通过step2执行以后，再执行step3，新链接上来的客户端信息，就能显示ip了)

select sid,serial\#,username,program,machine,client\_info    
from v$session    
where username is not null    
order by username,program,machine;     
  
  
7 9744  ORCL JDBC Thin Client abc 192.168.1.100  
8 9204  ORCL JDBC Thin Client abc 192.168.1.100  
10 3737  ORCL JDBC Thin Client abc 192.168.1.100  
11 3045  ORCL JDBC Thin Client abc 192.168.1.100  
......   
228 6140  ORCL JDBC Thin Client abc 192.168.1.100  
23 6487  ORCL navicat.exe WORKGROUP\\FRANK-PC 192.168.1.210  
34 4593  ORCL navicat.exe WORKGROUP\\FRANK-PC 192.168.1.210  
135 21825  ORCL navicat.exe WORKGROUP\\FRANK-PC 192.168.1.210  
......