oracle 数据库运维知识
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'