BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 500
SQL> alter system set processes=40 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 1987563520 bytes
Fixed Size 2254584 bytes
Variable Size 1224739080 bytes
Database Buffers 754974720 bytes
Redo Buffers 5595136 bytes
Database mounted.
Database opened.
SQL> select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
------------------------------ --------------- --------------------
processes 24 40
sessions 25 556
SQL> show parameter proce
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 40
processor_group_name string
SQL> select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
RESOURCE_NAME MAX_UTILIZATION LIMIT_VALUE
------------------------------ --------------- --------------------
processes 40 40
sessions 45 556
SQL>
客户端连接时,报下如的错误。
alter文件中的预警信息如下:
Thu May 14 15:39:32 2015
SMCO started with pid=37, OS id=19445
Thu May 14 15:44:30 2015
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.
Process m000 submission failed with error = 20
Thu May 14 15:49:30 2015
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.
Process m000 submission failed with error = 20
Thu May 14 15:59:30 2015
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.
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Thu May 14 16:00:30 2015
Process m000 submission failed with error = 20
Thu May 14 16:01:30 2015
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.
Process m000 submission failed with error = 20
Thu May 14 16:03:31 2015
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.
Process m000 submission failed with error = 20
Thu May 14 16:04:31 2015
Process m000 submission failed with error = 20
例子中我们可以清楚地了解到是因为数据库在实际运行中出现了processes进程总数达到参数设定上限从而导致问题出现,那么可以合理增加初始化参数processes或者通过修正异常频繁的程序客户端登录来解决该问题。