如何查看Oracle某时刻的客户端连接情况并显示客户端IP

 

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

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

 

2、缺省从 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; 
/

 

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

select sid,serial#,username,program,machine,client_info
from v$session
where username is not null
order by username,program,machine; 

  显示结果：

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的连接数

select count(*) from v$process;

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

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

 6、修改最大连接数

alter system set processes=300 scope=spfile;