生产库报ORA-00018： maximum number of sessions exceeded .
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
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.
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.
Abstract: ORA-18 AT 50% OF SESSIONS
Status: 92,Closed, Not a Bug
metalink的解释是：Oracle的internal recursive sessions 不会统计在 v$session视图中，当达到50%的limit session时会发生ORA-18告警.当用户对数据字典做修改时，会触发recursive session.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15592508/viewspace-722899/，如需转载，请注明出处，否则将追究法律责任。