ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-00018

ORA-00018

原创 Linux操作系统 作者:dukewyh 时间:2012-05-07 14:41:09 0 删除 编辑
生产库报ORA-00018: maximum number of sessions exceeded .
查看V$resource_limit视图,MAX_UTILIZATION值小于LIMIT_VALUE,疑惑为什么Oracle会报ORA-00018呢?
 
查看http://tangyunoracle.itpub.net/post/42756/527172
 
--------------------------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
metalink的解释是:Oracle的internal recursive sessions 不会统计在 v$session视图中,当达到50%的limit session时会发生ORA-18告警.当用户对数据字典做修改时,会触发recursive session.
 
session LIMIT_VALUE的设定该根据具体的应用进行设定。
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15592508/viewspace-722899/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-12

  • 博文量
    28
  • 访问量
    82386