ORA-00020: maximum number of processes (40) exceeded模拟会话连接数满
2024-09-04 21:52:29
问题描述：在正式生产环境中，有的库建的process和session连接数目设置的较小，导致后期满了无法连接。因为正式库无法进行停库修改，只能释放连接，做个测试模拟
1. 修改现有最大会话与进程连接数
SQL> alter system set processes = 35 scope = spfile; System altered. SQL> alter system set sessions = 40 scope = spfile; System altered.
2.重启生效,加大连接数
通过打开会话框以及连接plsql窗口制造连接数
制造到一定数目时，会发现sqlplus已经登陆不上去
后台日志：
ORA-00020: maximum number of processes (40) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
此时的连接数情况
SQL> select count(*) from v$process; COUNT(*)
----------
39 SQL> select count(*) from v$session; COUNT(*)
----------
36
3.删除无效session
<一> plsql删除会话
选中要删除的会话，删除完后在后台不会直接释放，在连接窗口执行命令会发现会话和已经被killed掉，然后这边后台释放完成
<二> sqlplus删除会话
查询会话
SQL> select username,status,wait_time,last_call_et from v$session; USERNAME STATUS WAIT_TIME LAST_CALL_ET
------------------------------ -------- ---------- ------------
ACTIVE 0 1874
ACTIVE 0 1873
ACTIVE 0 1873
ACTIVE 0 1873
ACTIVE 0 1869
ACTIVE 0 1868
ACTIVE 0 1858
SYS INACTIVE 0 1805
SYS ACTIVE -1 0
ACTIVE 0 1873
ACTIVE 0 1873 USERNAME STATUS WAIT_TIME LAST_CALL_ET
------------------------------ -------- ---------- ------------
ACTIVE 0 1873
ACTIVE 0 1868
SYSTEM INACTIVE 0 1826
ACTIVE 0 1858
SYS INACTIVE 0 1711
SYS INACTIVE 0 826
ACTIVE 0 1874
ACTIVE 0 1873
ACTIVE 0 1873
ACTIVE 0 1873
ACTIVE 0 1868 USERNAME STATUS WAIT_TIME LAST_CALL_ET
------------------------------ -------- ---------- ------------
ACTIVE 0 1868
SYS INACTIVE 0 1809
SYS INACTIVE 0 1705
SYS INACTIVE 0 1633
SYS INACTIVE 0 1417
ACTIVE 0 1874
ACTIVE 0 1873
ACTIVE 0 1873
ACTIVE 0 1873
SYS INACTIVE 0 1813
ACTIVE 0 1868 USERNAME STATUS WAIT_TIME LAST_CALL_ET
------------------------------ -------- ---------- ------------
SYS INACTIVE 0 1757
SCOTT INACTIVE 0 24
SYSTEM INACTIVE 0 1567 36 rows selected.
生成kill会话语句
SQL> select 'alter system kill session '''||sid||','||serial#||''';'from v$session where LAST_CALL_ET>500 AND status='INACTIVE'; 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '11,1';
alter system kill session '26,21';
alter system kill session '28,29';
alter system kill session '29,7';
alter system kill session '51,3';
alter system kill session '52,9';
alter system kill session '53,9';
alter system kill session '64,9';
alter system kill session '69,17';
alter system kill session '73,31';
alter system kill session '74,5'; 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '75,13'; 12 rows selected.
执行sql语句
SQL> alter system kill session '11,1';
SQL> alter system kill session '26,21';
SQL> alter system kill session '28,29';
SQL> alter system kill session '29,7';
SQL> alter system kill session '51,3';
SQL> alter system kill session '52,9';
SQL> alter system kill session '53,9';
SQL> alter system kill session '64,9';
SQL> alter system kill session '69,17';
SQL> alter system kill session '73,31';
SQL> alter system kill session '74,5';
在后台其中一个连接会话执行命令，已经语法执行
SQL> archive log list;
ORA-00028: your session has been killed
<三> 操作系统kill会话
查询不活跃会话的sid，转换为操作系统的pid，通过操作系统进行kill掉进程
SQL> select username,sid,serial#,status,last_call_et from v$session where status='INACTIVE'; USERNAME SID SERIAL# STATUS LAST_CALL_ET
------------------------------ ---------- ---------- -------- ------------
SYS 11 3 INACTIVE 340
SYS 28 31 INACTIVE 258
SYS 51 5 INACTIVE 267
SYS 52 11 INACTIVE 255
SYS 53 11 INACTIVE 253
SYS 69 19 INACTIVE 261
SCOTT 74 7 INACTIVE 7
SYSTEM 75 13 INACTIVE 3429
sid转换为spid
select spid from v$process where addr in (select paddr from v$session where sid=11);
select spid from v$process where addr in (select paddr from v$session where sid=28);
select spid from v$process where addr in (select paddr from v$session where sid=51);
select spid from v$process where addr in (select paddr from v$session where sid=52);
select spid from v$process where addr in (select paddr from v$session where sid=53);
select spid from v$process where addr in (select paddr from v$session where sid=69);
select spid from v$process where addr in (select paddr from v$session where sid=74);
select spid from v$process where addr in (select paddr from v$session where sid=75);
操作系统进程kill掉
[oracle@orcl ~]$ kill -9 5294
[oracle@orcl ~]$ kill -9 5391
[oracle@orcl ~]$ kill -9 5384
-bash: kill: (5384) - No such process
[oracle@orcl ~]$ kill -9 5393
[oracle@orcl ~]$ kill -9 5395
[oracle@orcl ~]$ kill -9 5389
[oracle@orcl ~]$ kill -9 5413
[oracle@orcl ~]$ kill -9 4996
查看现有连接，已经被释放
SQL> archive log list;
ORA-03135: connection lost contact
查看连接数据库连接数
SQL> select count(*) from v$session;
COUNT(*)
----------
29
SQL> select count(*) from v$process;
COUNT(*)
----------
35
<四> 增加进程和会话数，不过要停库
1.查看processes和sessions参数 show parameter processes
show parameter sessions
select count(*) from v$session ; --当前的session连接数
select count(*) from v$process ; --当前的数据库连接数
select value from v$parameter where name ='processes'; --数据库允许的最大连接数 2.修改processes和sessions值 alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;
3.重启数据库 shutdown immediate;
startup;
最新文章
- 在一个空ASP.NET Web项目上创建一个ASP.NET Web API 2.0应用
- 初学C#和MVC的一些心得,弯路,总结,还有教训(3)--Dapper
- ubuntu14.10建立热点wifi分享给手机
- HBASE解析
- ios下,对于position:fixed支持不完美的额解决方案
- PHP5.4最新特性
- (KEIL)MDK5安装与JLINK问题解决方法(支持代码自动补全)
- c#代码使用ResourceDictionary样式
- Pascal's Triangle II
- ubuntu thinkphp pathinfo 404等问题
- 阿里云服务器重启后mysql不能启动的问题
- 设计模式(3)-对象创建型模式-Abstract Factory模式
- 2014.first[未填]
- url语法
- 面试题之-----String,StringBuffer，StringBuilder的区别
- iOS原生 和 react native视图混编
- jetbrain_ia
- [luogu P3628] [APIO2010]特别行动队
- magento登陆
- HDU 1233：还是畅通工程（最小生成树）