Oracle 11gR1 dbca – maximum number of sessions exceeded

Oracle11g R1 (11.1.0.6)建库的时候遇到以下问题，此问题是BUG:8343487引起的：

ORA-12801: error signaled in parallel query server P073.
ORA-00018: maximum number of sessions exceeded
ORA-06512: at “SYS.UTL_RECOMP”, line 629
ORA-06512: at “SYS.UTL_RECOMP”, line 671
ORA-06512: at line 1

如果还没有建库则可通过设置parallel server相关的参数parallel_max_servers及parallel_min_servers参数解决此问题，此参数在DBCA建库调用的模板文件中设置，默认位置$ORACLE_HOME/assistants/dbca/templates/的xx.dbc文件中。

PARALLEL_MAX_SEVERS参数设置并行执行可用的最大进程数量，该参数的缺省值如下得出：
1.当PGA_AGGREGATE_TARGET >0时
PARALLEL_MAX_SERVERS= (CPU_COUNT x PARALLEL_THREADS_PER_CPU x 10)

2.当PARALLEL_MAX_SERVERS未设置
PARALLEL_MAX_SERVERS=(CPU_COUNT x PARALLEL_THREADS_PER_CPU x 5)

缺省设置可能并不足够，通常我们根据最高的并行度（DOP）来设置PARALLEL_MAX_SERVERS参数：

2 x DOP x NUMBER_OF_CONCURRENT_USERS

如果已经建库则参考metalink文章ID 749359.1                    

Number of Sessions Exceeded During Database Creation While Executing SYS.UTL_RECOMP [ID 749359.1]
	修改时间 14-JAN-2010     类型 PROBLEM     状态 PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 9.2.0.8
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.8
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.1 to 9.2.0.8
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.1.0.8

Symptoms

During the installation of  Oracle 11G, selected the option to create a new "General" database.  During the database creation,  encountered the following:

# pg postDBCreation.log
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 801939456 bytes
Fixed Size 2099064 bytes
Variable Size 197589128 bytes
Database Buffers 524288000 bytes
Redo Buffers 77963264 bytes
Database mounted.
Database opened.
utl_recomp_begin: 10:55:57
BEGIN utl_recomp.recomp_serial(); END;

*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P023
ORA-00018: maximum number of sessions exceeded
ORA-06512: at "SYS.UTL_RECOMP", line 629
ORA-06512: at "SYS.UTL_RECOMP", line 671
ORA-06512: at line 1

It is also possible to receive ORA-00020 errors in this scenario as well..

Cause

PARALLEL_MAX_SERVERS was set to 5.   Sessions exhausted due to parallelism. 

UTL_RECOMP spawned sessions and processes to parallelize completion and consumes sessions beyond the default. 
This problem is due to BUG:8343487 - UTLRP.SQL EXECUTING IN PARALLEL REGARDLESS OF SETTING.

Solution

Try one or both of the following:
1.  Before creating database, set parallel parameters to zero.
     PARALLEL_MAX_SERVERS=0  

     PARALLEL_MIN_SERVERS =0
2.  Set number of sessions to a higher number than default,  i.e.,  SESSIONS=500

References

BUG:8343487 - UTLRP.SQL EXECUTING IN PARALLEL REGARDLESS OF SETTING