ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded，提示超出最大連線數，但是查詢v$session卻發現連線數不到設定值的一半，實際上已經達到了設定的session了，由於裡面的一些session做了遞迴或者其他呼叫開啟新的session，但在v$session中是查不到的。

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 8月 7 23:55:28 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn / as sysdba
ERROR:
ORA-01075: 您現在已登入
----------alert----------------------------------
Sun Aug 07 23:11:38 2011
Errors in file /home/oracle/db/diag/rdbms/infoxdb/infoxdb/trace/infoxdb_m004_2621922.trc:
ORA-00018: 超出最大會話數
Sun Aug 07 23:11:41 2011
Errors in file /home/oracle/db/diag/rdbms/infoxdb/infoxdb/trace/infoxdb_m002_2400438.trc:
ORA-00018: 超出最大會話數
------------------------trace----------------------------------
$ more /home/oracle/db/diag/rdbms/infoxdb/infoxdb/trace/infoxdb_m002_2400438.trc
Trace file /home/oracle/db/diag/rdbms/infoxdb/infoxdb/trace/infoxdb_m002_2400438.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /home/oracle/db/product/11.1.0/db_1
System name: AIX
Node name: GDGZ-SMG-IS3-SV04C-SMSVR4
Release: 3
Version: 5
Machine: 00C830D44C00
Instance name: infoxdb
Redo thread mounted by this instance: 1
Oracle process number: 126
Unix process pid: 2400438, image: (m002)
*** 2011-08-07 23:11:41.060
*** SESSION ID:(10.1894) 2011-08-07 23:11:41.060
*** CLIENT ID:() 2011-08-07 23:11:41.060
*** SERVICE NAME:(SYS$BACKGROUND) 2011-08-07 23:11:41.060
*** MODULE NAME:(MMON_SLAVE) 2011-08-07 23:11:41.060
*** ACTION NAME:(Auto-CPUUSAGE Action) 2011-08-07 23:11:41.060
KSV 18 error in slave process
*** 2011-08-07 23:11:41.060
ORA-00018: M-3M-,M-3M-vM-WM-nM-4M-sM-;M-aM-;M-0M-JM-}
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00018: maximum number of sessions exceeded
--------------------------metalink-----------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Checked for relevance on 27-Apr-2010
Symptoms
Following errors are reported in alert.log file.
ORA-00604: error occurred at recursive SQL level 1 and
ORA-00018: maximum number of sessions exceeded
At the time of the errors, the number of sessions from ‘select count(*) from v$session’ is much less than the SESSIONS parameter value set in the database (either set directly by SESSIONS parameter or derived from PROCESSES parameter).
One may also observe the error despite the fact that the SESSIONS_MAX from v$license has not been reached.
Cause：
The reason is that internal recursive sessions are not reflected in the v$session view (and also not likely in the v$license view). A bug was filed for a similar issue where ORA-18 was occurring when 50% of the sessions limit was reached. The bug was closed as "not a bug", and it was explained that internal recursive sessions can account for part of the limit but aren't seen in the dictionary views.
Recursive sessions are an essential part of the normal functioning of the RDBMS. It is impossible to identify every circumstance that would require such sessions, but in general, if a user-initiated operation requires the manipulation of data dictionary objects, then recursive sessions may get created. To take a simple example, say you create a table while logged in as some ordinary user. Behind the scenes this has to insert rows into obj$, tab$ etc which are owned by the SYS user. Since a normal user would have no privilege to insert into these objects, a recursive session is created which logs in as SYS.
Ref：Bug 1528019
Abstract: ORA-18 AT 50% OF SESSIONS
Status: 92,Closed, Not a Bug
Eg:
SQL> select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;
COUNT(*)
----------
10
SQL> select count(*) from v$session;
COUNT(*)
----------
9
Solution
Increase the init.ora SESSIONS parameter.
Recommendation is to preserve 50% of the SESSIONS value for recursive sessions. So, for example if
it is expected to have 30 client sessions open, then set the SESSIONS parameter to 60.
SQL> alter system set SESSIONS=60 scope=spfile;
References
BUG:1528019 - ORA-18 AT 50% OF SESSIONS
NOTE:30794.1 - Init.ora Parameter "PROCESSES" Reference Note
NOTE:30808.1 - Init.ora Parameter "SESSIONS" Reference Note
-----------------metalink--------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.2 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
Checked for relevance on 19-Nov-2010
Symptoms
Instance was terminated by PMON due to error ORA-00443
Alert Log:
Wed Oct 10 07:53:16 2007
Errors in file G:oracleadminOMO1APPSbdumpomo1appsPMON.TRC:
ORA-00443: background process "SNP1" did not start
.PMON: terminating instance due to error 443
Wed Oct 10 07:55:37 2007
Instance terminated by PMON, pid = 3848
Dump file G:oracleadminOMO1APPSbdumpomo1appsALRT.LOG
trace file:
*** 2007-10-10 07:39:34.578
*** SESSION ID:(533.23092) 2007-10-10 07:39:34.062
error 18 detected in background process
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00018: maximum number of sessions exceeded
*** 2007-10-10 07:47:49.843
*** SESSION ID:(533.23128) 2007-10-10 07:47:49.421
error 18 detected in background process
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00018: maximum number of sessions exceeded
Cause
It looks like the problem here is resources problem (the number of sessions exceeded).
That is why the SNP process died, and also that is why it failed to restart.
Solution
Edit the seesions parameter in the pfile to a larger value
Let's say increase by 20 %
Or if using spfile, then do the following:
SQL> alter system set sessions= scope=spfile;
SQL> shutdown immediate
SQL> startup
References
BUG:2246901 - SNP PROCESSES GETTING RESTARTED EVERY 5 MINUTES
---------------End------------------------------------------