ORA-00020: maximum number of processes (40) exceeded模拟会话连接数满

问题描述:在正式生产环境中,有的库建的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

 

<四> ALTER SYSTEM DISCONNECT SESSION

POST_TRANSACTION表示等待事务完成后断开会话,IMMEDIATE表示中断会话,立即回滚事务。使用immediate可以立即释放会话
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

 

<五> 增加进程和会话数,不过要停库

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;

 

 

 

 

 

posted @ 2020-11-12 16:16  我爱睡莲  阅读(387)  评论(0编辑  收藏  举报