查看Oracle某时刻的客户端IP连接情况

1、查看每个oracle帐户的连接总数

Sql代码 

 select username,count(username) from v$session where username is not null group by username

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

Sql代码  

create or replace trigger on_logon_trigger after logon on database  
begin  
'userenv', 'ip_address'));  
end;   
/

3、比较常用的显示客户端信息的sql：

Sql代码  

select sid,serial#,username,program,machine,client_info  
from v$session  
where username is not null  -- and status = 'ACTIVE'
order by username,program,machine;

  显示结果：

Java代码  

1   102 2986    AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
2   375 10078   AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
3   858 1751    AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
4   761 5406    AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
5   803 3590    AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
6   448 2525    AMS_TEST    JDBC Thin Client    MICROSO-V22G1JA 192.168.0.92  
7   654 10009   HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115  
8   659 9547    HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115  
9   664 7554    HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115  
10  693 6960    HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115  
11  714 3672    HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115  
12  724 13457   HDCZOA  JDBC Thin Client    WWW-18843A7DA64 192.168.0.115

4、查看当前Oracle的连接数

Sql代码  

select count(*) from v$process;

 5、查看数据库允许的最大连接数

Sql代码  

select value from v$parameter where name='processes';

 6、修改最大连接数

Sql代码  

alter system set processes=3000 scope=spfile;

7、业务IP连接数查询

set pagesize 1000 line 1000 

select count(1), client_info from v$session where username is not null group by client_info order by 1 desc;

日积月累