必须用分页、游标或流式fetch控制内存，避免PGA爆满；REF CURSOR交由客户端分页最稳妥，DBMS_SQL适合服务端动态分页，需调优pga_aggregate_limit等参数防OOM。
直接结论：不要让存储过程一次性返回几万行以上结果集，oracle pga 会爆，客户端也扛不住。必须用分页、游标或流式 fetch 控制内存占用。
为什么 SELECT * FROM huge_table 在存储过程中会 OOM？
存储过程里如果用 OPEN cur FOR SELECT ... 返回一个大结果集，Oracle 会在 PGA 中为该游标分配内存缓存所有行（尤其在 PL/SQL 块中用 FETCH BULK COLLECT INTO 时）。PGA 不共享，每个会话独占，且默认无硬上限——但操作系统物理内存和 pga_aggregate_limit 会掐断它。
- 常见错误现象：
ORA-04030: out of process memory或客户端报“内存不足”、“连接中断” - 实际场景：报表导出、ETL 中间抽取、BI 工具直连调用存储过程
- 关键点：
BULK COLLECT LIMIT n不是“限制返回行数”，而是“每次 fetch 最多取 n 行”，但整个结果集仍需在 PGA 中暂存——除非显式关闭游标或用REF CURSOR交由客户端分批消费
用 REF CURSOR + 客户端分页是最稳妥的方案
把结果集控制权交给调用方，避免服务端 PGA 累积。存储过程只负责打开游标，不 fetch、不 collect。
- 存储过程定义要返回
SYS_REFCURSOR类型，例如：CREATE OR REPLACE PROCEDURE get_orders(p_cur OUT SYS_REFCURSOR) AS BEGIN OPEN p_cur FOR SELECT order_id, cust_name FROM orders WHERE status = 'SHIPPED'; END; - 调用方（如 Java JDBC）用
ResultSet配合setFetchSize(n)控制每次网络批次，Oracle 服务端 PGA 只维持当前 fetch 批次所需内存 - 切忌在存储过程中对
REF CURSOR做FETCH ... BULK COLLECT——这等于又把压力拉回服务端 - 注意：
REF CURSOR必须由客户端显式close()，否则游标长期持有 PGA 内存，可能触发ORA-01000: maximum open cursors exceeded
DBMS_SQL 动态游标更适合复杂分页逻辑
当需要根据参数动态拼 SQL、且必须在存储过程中完成分页（比如无法暴露原始表给客户端），DBMS_SQL 比隐式游标更可控，能精确管理内存生命周期。
- 用
DBMS_SQL.OPEN_CURSOR→PARSE→DEFINE_COLUMN→EXECUTE→FETCH_ROWS循环，每次只处理一批（如 1000 行） - 每轮
FETCH_ROWS后立即处理、清空集合变量（COLLECT的数组），再DBMS_SQL.CLOSE_CURSOR彻底释放 PGA - 对比隐式游标：
DBMS_SQL不自动缓存全结果集，内存峰值取决于FETCH_ROWS参数，而非总行数 - 风险点：
DBMS_SQL.VARCHAR2_TABLE等集合类型仍有上限（默认 255 字符），长文本字段要用DBMS_SQL.VARCHAR2A或分块读取
PGA 相关参数不能只靠默认值
即使用了游标分页，高峰期并发高时 PGA 仍可能被挤爆。必须结合实际负载调优，而不是依赖 pga_aggregate_target 的自动管理。
-
pga_aggregate_limit是硬上限（单位 bytes），建议设为物理内存的 20%～30%，防止 Oracle 吃光系统内存导致 OS kill 进程 -
workarea_size_policy设为AUTO（默认），确保排序、哈希等操作受pga_aggregate_target约束；设为MANUAL会绕过限制，极其危险 - 查当前 PGA 使用：
SELECT * FROM v$pgastat；看单个会话：SELECT pid, program, pga_used_mem, pga_max_mem FROM v$process ORDER BY pga_max_mem DESC - 容易被忽略的点：RAC 环境下每个实例独立计算 PGA 上限，不能按总内存均分；共享服务器模式（MTS）下 UGA 放在 SGA 中，PGA 概念弱化，但
large_pool_size必须足够
真正难的不是写出来，而是判断“哪一层该做分页”——存储过程、中间件、还是前端？只要数据不出 Oracle 实例，就别让它在 PGA 里堆着。游标没 close，内存就不会还。
