1、在数据库中连接用某个用户连接数据库报错:
Product: DbVisualizer Pro 9.1.1
Build: #2063 (2013/10/01 12:27)
Java VM: Java HotSpot(TM) Client VM
Java Version: 1.7.0_40
Java Vendor: Oracle Corporation
OS Name: Windows 7
OS Arch: x86
OS Version: 6.1
An error occurred while establishing the connection:
Long Message:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Details:
Type: java.sql.SQLException
Error Code: 2391
SQL State: 61000
但是用另一个用户就可以登录使用。
查询问题原因:
1、查询用户连接数,连接方式(jdbc还是用户client),每个用户的连接数。
SELECT username , COUNT(*), machine FROM v$session WHERE username IS NOT NULL GROUP BY username, machine ORDER BY username;
2、--当前的数据库连接数
select count(*) from v$process ;
3、--数据库允许的最大连接数
select value from v$parameter where name = 'processes';
4、--修改最大连接数:
alter system set processes = 300 scope = spfile;
5、--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', b.sql_text,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;
SELECT osuser, a.username,cpu_time/executions/1000000||'s', b.sql_text,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;
6.查询用户连接数,连接方式(jdbc还是用户client),每个用户的连接数。
select DISTINCT username ,count(*) from v$session group by username order by username
7. 查看某个用户正在对数据库进行的操作。
select * from v$session where username='username' and status='ACTIVE'