項(xiàng)目經(jīng)理反饋，現(xiàn)場(chǎng)已做了單個(gè)用戶的最大連接數(shù)2000的限制，但數(shù)據(jù)庫(kù)還是報(bào)無(wú)法連接，故障用戶的連接數(shù)已3800多了。

查看日志報(bào)錯(cuò)如下

2023-07-20T13:07:57.794653+08:00
Process m000 submission failed with error = 20
Process m000 submission failed with error = 20
Process m000 submission failed with error = 20
Process m000 submission failed with error = 20
2023-07-20T13:08:16.882441+08:00
ORA-00020: maximum number of processes (5000) 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.
2023-07-20T13:08:57.846261+08:00
Process m001 submission failed with error = 20
Process m002 submission failed with error = 20
2023-07-20T13:09:15.712546+08:00
Process J000 submission failed with error = 20
2023-07-20T13:09:15.712780+08:00
kkjcre1p: unable to spawn jobq slave process
2023-07-20T13:09:15.712950+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_3577.trc:

2023-07-20T13:12:20.920401+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_3577.trc:
2023-07-20T13:12:22.930463+08:00
ORA-00020: maximum number of processes (5000) 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.
2023-07-20T13:12:58.049562+08:00
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Process m000 submission failed with error = 20
Process m000 submission failed with error = 20
2023-07-20T13:13:25.510459+08:00
ORA-00020: maximum number of processes (5000) 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.

故障用戶的當(dāng)前連接數(shù)已3880了

查看默認(rèn)的PROFILE，SESSIONS_PER_USER也確實(shí)做了2000限制

處理辦法：

1、SQL> alter system set resource_limit =TRUE;?? --對(duì)資源類的參數(shù)限制必須啟用該參數(shù)

?????? SQL> alter profile DEFAULT limit sessions_per_user 2000;???? ---事先已執(zhí)行過(guò)，可不執(zhí)行

2、kill用戶USR_LTWX的非活動(dòng)session

3、持續(xù)觀察，該用戶最大連接數(shù)沒(méi)超過(guò)2000，

4、讓現(xiàn)場(chǎng)經(jīng)理通知用戶產(chǎn)品排查故障原因