Oracle数据库 ORA-00157 错误分析和解决

好的，我们来全面解析 ORA-00157 错误。

ORA-00157: 超出最大会话数限制

1. 错误信息结构组成说明

一个典型的 ORA-00157 错误信息格式如下：

ORA-00157: maximum number of sessions exceeded

• 错误代码 (Error Code): ORA-00157
• 错误消息 (Error Message): maximum number of sessions exceeded
• 参数 (Parameters): 此错误消息不包含动态参数，是一个静态的明确声明。

这个结构非常简单直接：数据库已达到其允许的最大会话数，无法再创建新的会话连接。

2. 官方正式说明

原因 (Cause)

Oracle官方文档指出，此错误的发生是由于：

尝试建立到Oracle数据库的新连接（会话）时，数据库当前的会话总数已经达到了 SESSIONS 初始化参数所设定的硬性限制。

每个连接到数据库的客户端（用户应用程序、后台进程、管理工具等）都会占用一个会话。数据库管理员（DBA）通过 SESSIONS 参数预先定义了数据库实例能够同时支持的最大会话数量。这是一种保护机制，防止系统因连接过多而导致资源耗尽、性能急剧下降甚至崩溃。

场景 (Scenarios)

1. 应用程序连接池配置不当： 最常见的场景。应用程序（如Web服务器）使用的连接池最大连接数设置过高，且远超过数据库的 SESSIONS 限制。当流量高峰到来时，连接池创建所有连接，瞬间耗尽数据库的会话资源。
2. 连接泄漏 (Connection Leak): 应用程序中存在Bug，导致在完成数据库操作后没有正确关闭连接（调用.close()方法）。这些未被释放的连接会一直占用会话名额，最终累积到上限。
3. 突发的高并发访问： 例如，电商网站举行秒杀活动，大量用户同时访问，创建了大量并发会话，超过了日常配置的容量。
4. 共享服务器模式 (Shared Server) 资源不足： 在共享服务器架构下，SESSIONS 参数与 SHARED_SERVERS 和 DISPATCHERS 参数配置不当，导致可用的调度进程无法处理涌入的连接请求，虽然可能并未达到真正的 SESSIONS 上限，但表现出的现象类似。
5. 系统级或后台进程会话： 除了用户会话，Oracle自身的后台进程、作业调度（DBMS_JOB / DBMS_SCHEDULER）等也会占用会话名额。

相关原理 (Related Principles)

• 会话 (Session) vs. 进程 (Process): 一个会话是数据库中的一个逻辑连接实体，而一个进程是操作系统级别的物理执行单位。在专有服务器模式下，一个会话通常对应一个服务器进程。在共享服务器模式下，多个会话共享一个服务器进程池。
• SESSIONS 与 PROCESSES 参数的关系： SESSIONS 参数的值派生自 PROCESSES 参数。通常，SESSIONS = (1.1 * PROCESSES) + 5。PROCESSES 定义了能同时连接到实例的操作系统用户进程数（包括后台进程）。因此，调整 PROCESSES 是调整 SESSIONS 的基础。
• 资源管理： 会话会消耗PGA（程序全局区）内存、CPU时间和其它系统资源。SESSIONS 参数是一种关键的资源管控机制。

相关联的其他ORA-错误

• ORA-00018: maximum number of sessions exceeded - 这是一个非常古老的错误码，后来被 ORA-00157 取代，但含义完全相同。
• ORA-00020: maximum number of processes (string) exceeded - 这是更底层的错误。当尝试创建的进程数超过 PROCESSES 参数限制时触发。通常先发生 ORA-00020，然后才可能发生 ORA-00157。
• ORA-12516: TNS:listener could not find available handler with matching protocol stack - 监听器无法找到可用的数据库服务处理器。这个错误经常是数据库端达到会话或进程限制时，反馈给客户端连接请求的错误信息，是 ORA-00157 在客户端的一种常见表现形式。
• ORA-12519: TNS:no appropriate service handler found - 与ORA-12516类似，也是客户端连接时因服务器端资源（会话/进程）不足而收到的错误。

3. 定位原因与分析过程

1. 确认错误： 首先确认错误是 ORA-00157 或其在客户端的表现形态（如 ORA-12516）。

2. 检查当前会话数和配置限制：
   连接到数据库（如果还能连接的话，通常DBA会保留管理连接），执行以下SQL：

   -- 查看当前活动的会话数
   SELECT COUNT(*) AS current_sessions FROM v$session;
   
   -- 查看数据库允许的最大会话数和进程数
   SELECT name, value FROM v$parameter WHERE name IN ('sessions', 'processes');
   
   -- 或者更详细地查看使用情况
   SELECT resource_name, current_utilization, max_utilization, limit_value
   FROM v$resource_limit
   WHERE resource_name IN ('sessions', 'processes');
   

   如果 current_utilization 或 max_utilization 非常接近 limit_value，则证实了该问题。

3. 分析会话来源：

   -- 查看会话都是由哪些程序、用户、机器发起的
   SELECT program, username, machine, COUNT(*)
   FROM v$session
   GROUP BY program, username, machine
   ORDER BY COUNT(*) DESC;
   

   这可以帮助你识别是否是某个特定应用（如JDBC Thin Client、某个应用服务器主机）创建了异常多的连接。

4. 检查连接池设置： 查看应用程序的配置文件，确认其连接池的最大大小（Max Pool Size）设置。

5. 检查是否存在连接泄漏： 观察长时间处于 INACTIVE 状态但又未关闭的会话。

   SELECT sid, serial#, username, program, machine, status, last_call_et
   FROM v$session
   WHERE status = 'INACTIVE'
   AND last_call_et > 3600 -- 例如，闲置超过1小时
   AND username IS NOT NULL; -- 排除后台进程
   

4. 解决方案与相关SQL

解决方案分为临时应急和根本解决两大类。

临时应急方案 (Quick Fix)

1. 释放空闲会话 (需要DBA权限):

   -- 1. 找到要终止的会话（例如，那些闲置非常久的应用会话）
   SELECT sid, serial#, username, program, machine, status
   FROM v$session
   WHERE username = 'PROBLEM_USER' -- 替换为具体的用户名
   AND status = 'INACTIVE';
   
   -- 2. 逐个终止会话 (用上面查到的SID和SERIAL#)
   ALTER SYSTEM KILL SESSION 'sid, serial#';
   
   -- 或者使用更强大的IMMEDIATE选项
   ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;
   

   注意： 谨慎操作，确保不会终止关键的业务会话。

根本解决方案 (Permanent Solution)

1. 调整数据库参数 (需要DBA权限，可能需要重启数据库):
   这是最直接的解决方案。增加 PROCESSES 和 SESSIONS 参数的上限。

   -- 首先检查当前值，评估需要增加到多少
   SHOW PARAMETER processes
   SHOW PARAMETER sessions
   
   -- 修改参数 (例如，将进程数增加到600)
   ALTER SYSTEM SET processes=600 SCOPE=spfile;
   ALTER SYSTEM SET sessions=665 SCOPE=spfile; -- 通常 sessions = 1.1 * processes + 5
   
   -- 然后必须重启数据库使更改生效
   SHUTDOWN IMMEDIATE
   STARTUP
   

   警告： 增加这些参数会消耗更多的系统内存（尤其是PGA），请确保服务器有足够的内存资源。

2. 优化应用程序：

   • 调整连接池配置： 减小应用程序连接池的最大大小，使其远低于数据库的 SESSIONS 限制，并设置合理的空闲超时时间。
   • 修复连接泄漏： 审查应用程序代码，确保在所有代码路径（包括异常处理分支）中，数据库连接都被正确关闭（使用 try-with-resources 或 finally 块）。

3. 使用共享服务器模式 (Optional):
   对于大量并发短连接的应用，可以考虑使用共享服务器模式来减少资源消耗。但这会增加架构的复杂性，需要仔细规划和测试。

4. 实施资源管理器 (Resource Manager):
   对于多租户环境或混合 workload，可以使用Oracle资源管理器来限制不同用户组或PDB所能使用的会话数，避免一个应用耗尽所有资源。

5. 通俗易懂的语言讲解

把Oracle数据库想象成一家只有固定数量座位的热门餐厅（比如50个座位）。

• 会话 (Session) 就像是一个顾客占用的一个座位。
• SESSIONS 参数 就是餐厅的总座位数（50个）。
• PROCESSES 参数 大致相当于餐厅的总餐位数量，它决定了后厨和服务员能应付的最大顾客流。

ORA-00157错误就像是：
晚上七点，餐厅已经座无虚席（当前会话数 = 50）。这时又来了一个新顾客想要进门。领班就会拦住他说：“非常抱歉，我们已经满员了（maximum number of sessions exceeded）”，无法接待您。

为什么会发生这种情况？

1. 旅行团突然来袭： 一个50人的旅行团没有预定就直接来了（应用程序连接池配置过大，一次性创建50个连接）。
2. 有人占着茅坑不拉屎： 很多顾客吃完饭后，还在座位上聊天刷手机，几个小时都不走（连接泄漏，应用程序没有关闭连接）。
3. 餐厅本身太小： 餐厅只有50个座位，但它的名气却越来越大，平时来的顾客经常超过50人（数据库参数 SESSIONS 设置得太低，无法满足正常的业务增长）。

怎么解决？

1. 临时办法（请人离开）： 经理亲自去跟那些已经吃完很久的顾客沟通，请他们离开，为等待的顾客腾出座位（DBA登录数据库，杀死一些空闲的会话）。
2. 永久办法（扩大营业面积）： 餐厅老板决定把隔壁店面也盘下来，打通墙壁，将座位数从50个增加到100个（DBA修改 PROCESSES 和 SESSIONS 参数并重启数据库）。当然，这需要招聘更多厨师和服务员（消耗更多服务器内存和CPU）。
3. 管理顾客（优化应用）：
   • 要求旅行团必须提前预定，并且一次不能来超过30人（优化应用程序连接池配置，减小最大连接数）。
   • 在账单上印上“高峰时段用餐限时90分钟”的提示（配置连接池的超时时间，自动关闭闲置连接）。
   • 培训服务员，确保顾客离开后立刻清理桌面（修复应用程序代码中的连接泄漏Bug）。

所以，ORA-00157就是一个“客满”通知。解决方法要么是“请走一些客人”，要么是“扩建餐厅”，要么是“更好地管理顾客流量”。

欢迎关注我的公众号《IT小Chen》