ORA-00018: maximum number of sessions exceeded

今天朋友遭遇了如下的问题，远程联系我解决 SQL> select * from xxx ; select * from xxx                      ERROR at line 1: ORA-00018: maximum number of sessions exceeded 相信只要是干过一两年DBA的对与这个错误ORA-00018是比较熟悉的，也知道问题的解决办法，那就是增加processes的参数设置，由于sessions是依据processes推导出来的，因此processes的增大，会导致sessions也会增大，也就解决了这个问题。具体推导的公式，可能9I,10G版本基本遵循的是sessions=(1.1*processes)+5，11G以后（可能是11GR2之后，抱歉我没11GR1的版本来做测试），不是这样了，在我11GR2的环境下，3000 processes的设置，已经有4560个sessions,大大的增加了session的数量。 这个错大部分情况下，都是发生在建立连接的时候，由于超出了sessions的设置值，导致连接抛错。但是。。。 但是我朋友的这个案例是，他已经连接了！！！！ 其实这个问题不难回答，因为只要看过TOM书的同学都知道，一个连接，可能会导致多个session。 举个例子： 你计划统计下xxx表的总记录数 当时你的数据库满足了以下几个条件中的一个 在数据库刚启动的时候、或者flush shared pool之后，或者查询的表的数据字典信息已经不在共享池里 你建立了一个连接后，ORACLE分配给你一个session，我们称之为session1 然后你select count(*) from xxx的时候 Oracle会独立新开一个session，我们称之为session2 ,这个session2会去递归的查询数据字典（硬解析的需要），比如tab$,col$,seg$以及一些统计信息的数据字典基表。 等它做完这些工作后，就立即消失了 当然除了查询，还会很多操作会产生这种行为，比如create,drop，alter等等，由于这些操作都修改了数据字典基表，因此他们也会产生一个递归的session的来去帮他们完成这些数据字典的操作，而且你还会“惊奇”的发现，这些递归session的用户竟然不是你当时连接的用户。 比如你用test/test做的连接，但是递归语句的session的用户是sys!!!! 说到这里，你可能觉得，你大概知道了这里面的奥秘，那就是，我朋友当时发出这个查询的时候，连接session的数量刚好等于了允许存在session的最大数量，因此导致递归的session无法创建而报错。 在确认这个问题前，还有些问题要解决。 是不是查询都要递归的产生session ? 不是的，只有需要的数据字典信息不在数据字典cache里的时候。 当时那个环境下，查询其他常用的表会不会报错？ 经过我的测试，当时查询一些常用的表，是不报错的！！！因为它的数据字典信息都是在数据字典cache里的。 好，上面的疑问解决后，我们来看下，当时session的数量，和设置的最大值分别是多少 select count(*) from v$session;   COUNT(*) ----------        216 show parameter sessions NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ------------------------------                                             sessions                             integer                232 奇怪的事又发生了！ 当前session的数量还没达到设置的最大值，如果是要创建递归的session，那仅仅需要一个session就够了。而他的环境里，还剩余232-216=16个session可以用。 问题出哪了呢？ 我的猜测应该是v$session这个视图，对于递归的session做了过滤。 经过查询V$FIXED_VIEW_DEFINITION，我们可以知道gv$session是依据数据字典基表X$KSUSE创建出来的（创建语句我就不贴出来了） 可以看到创建的最后where语句后，过滤掉了一些内容，我数学没学好，不确认是不是过滤掉的递归的session ，但是我还有办法，继续往下看。 怎么来证明呢？看我的。 SESSION1： select sid from v$mystat where rownum=1;        SID ----------       1768 select paddr from v$session where sid=1768; PADDR ---------------- 0000000AE9530BD0 SESSION 2： lock table tab$ IN exclusive MODE; SESSION 1： CREATE TABLE A (ID NUMBER); 会被hang住，因为创建表需要往数据字典基表$tab里插入记录。而且这个工作是由递归产生session做的 session 2: select paddr from v$session where sid=1768; PADDR ---------------- 0000000AE9530BD0 select decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna  from x$ksuse s where ksusepro = '0000000AE9530BD0'; DECODE(BITAND(KSUSEF KSUUDSNA -------------------- ------------------------------------------------------------ USER                 ILSMP23ST RECURSIVE            SYS 上面测试的思路是：创建一张表，由于这种DDL，会往数据字典基表tab$里插入记录，如果我们把tab$表锁住，那么就可以观察到递归的session在那里被hang住等待锁，这样我们就能观察到这个“神奇”的会话了。 经过我们的测试发现：果然，v$session里只有一条记录，而数据字典基表里，已经有2个会话了，其中一个是递归产生的，sys用户的，而这个递归会话在v$session里没看到，也就是说过滤掉了。 说到这里，我们差不多知道这个案例的原因了 虽然v$session里总共的sessoin数量与设置的参数值之间还有16个session可以用，但是由于v$session屏蔽了递归session的数量，因此实际上，非常可能这16个你认为可以用的session早被递归的session占用了。 如果你看DSI比较多的话，特别是锁那一节，你会知道有一个视图其实已经暴露给我们，方便的查询各种资源的使用情况以及最大值设置情况。 select * from v$resource_limit; RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE ------------------------------ ------------------- --------------- -------------------- -------------------- processes                                      120             121       3000                 3000 sessions                                       205             206       4560                 4560 enqueue_locks                                   27              31      55020                55020 enqueue_resources                               22              22      20284            UNLIMITED ges_procs                                        0               0          0                    0 ges_ress                                         0               0          0            UNLIMITED ges_locks                                        0               0          0            UNLIMITED ges_cache_ress                                   0               0          0            UNLIMITED ges_reg_msgs                                     0               0          0            UNLIMITED ges_big_msgs                                     0               0          0            UNLIMITED ges_rsv_msgs                                     0               0          0                    0 gcs_resources                                    0               0  UNLIMITED            UNLIMITED gcs_shadows                                      0               0  UNLIMITED            UNLIMITED smartio_overhead_memory                          0               0          0            UNLIMITED smartio_buffer_memory                            0               0          0            UNLIMITED smartio_metadata_memory                          0               0          0            UNLIMITED smartio_sessions                                 0               0          0            UNLIMITED dml_locks                                        0               0      20064            UNLIMITED temporary_table_locks                            0               0  UNLIMITED            UNLIMITED transactions                                     0               0       5016            UNLIMITED branches                                         0               0       5016            UNLIMITED cmtcallbk                                        0               0       5016            UNLIMITED max_rollback_segments                            0               0       5016                65535 sort_segment_locks                               0               0  UNLIMITED            UNLIMITED k2q_locks                                        0               0       9120            UNLIMITED max_shared_servers                               1               1  UNLIMITED            UNLIMITED parallel_max_servers                             0               0       1600                 3600 27 rows selected. select count(*) from v$session;   COUNT(*) ----------        116 v$resource_limit里显示的第二行，列为CURRENT_UTILIZATION 的值，就是真正的已经被占用的sessoin，这个值比较我查询v$session多出了很多，这些多余的session就是递归的session。

number,  select,  from,  ERROR,  line