oracle 19c中递归with查询本身不生成永久段，但失控时会大量占用temp、pga、undo及sysaux空间；根本原因是资源误配或逻辑失控，而非语法缺陷，必须通过深度限制、执行路径监控和系统表空间清理来防控。
oracle 19c 中 with 递归查询本身不直接导致段空间扩展，但不当使用会触发大量临时段（temp）分配、pga暴涨甚至写入 sysaux/undo，最终表现为“段空间扩展开销”——本质是资源误配或逻辑失控，不是语法缺陷。
为什么递归查询会引发段空间问题
递归 CTE 不生成永久段，但它在执行过程中依赖：临时表空间（排序、哈希、中间结果）、PGA（递归栈和缓冲）、UNDO（事务一致性）、甚至 SYSAUX（如统计信息收集或 SQL Plan Baseline 自动捕获）。当递归失控时：
- 临时段持续增长，
v$sort_segment显示USED_SPACE持续上升，且tempfile扩展频繁 - PGA 分配激增，触发
ORA-04030，trace 中出现ksm sseg osctx和fallocate failed - 若递归中含 DML 或隐式统计收集（如首次执行带
/*+ MONITOR */），可能写入WRI$_SQLSET_PLAN_LINES等 SYSAUX 对象，导致ORA-1653报错 - UNDO 表空间被长事务撑满（例如递归更新未提交），间接阻塞其他会话的段扩展请求
限制递归深度并监控实际执行路径
不设终止条件的递归等于给数据库发“无限内存申请单”。必须用 LEVEL 或显式计数器硬性截断：
WITH RECURSIVE org AS ( SELECT emp_id, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id WHERE o.depth
-
WHERE条件必须放在递归分支内（即UNION ALL右侧），否则优化器可能下推失效，仍做全树遍历 - 超过 30 层的组织架构极少见；BOM 场景建议设为 20，权限继承建议 ≤ 15
- 执行前加
/*+ MATERIALIZE */提示可避免重复计算，但会增加 TEMP 使用；权衡后优先保深度控制
检查并清理已被递归污染的临时/系统表空间
一旦发生过失控递归，残留影响常藏在 TEMP 和 SYSAUX：
- 查当前大临时段：
SELECT segtype, contents, blocks FROM v$sort_usage ORDER BY blocks DESC;—— 若segtype = 'SORT'且blocks > 100000，说明有未释放中间结果 - 确认 TEMP 文件是否自动扩展：
SELECT file_name, autoextensible, maxbytes/1024/1024 AS max_mb FROM dba_temp_files;—— 若autoextensible = 'NO'，需先ALTER DATABASE TEMPFILE ... RESIZE或加新 tempfile - SYSAUX 异常增长？查源头：
SELECT segment_name, bytes/1024/1024 AS mb FROM dba_segments WHERE tablespace_name = 'SYSAUX' AND bytes > 100*1024*1024 ORDER BY 2 DESC;—— 若WRI$_SQLSET_PLAN_LINES排前三，说明自动 SQL 计划捕获被递归语句刷爆，应临时禁用：ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=FALSE;
避免递归中隐式触发 UNDO 和统计写入
看似只读的递归查询，在特定配置下会悄悄写库：
- 关闭统一审计对 CTE 的捕获（如果不需要）：
AUDIT SELECT ON sys.dba_tables BY ACCESS;这类语句可能因权限检查写AUD$，而AUD$若仍在 SYSTEM 表空间，会加剧ORA-01653 - 禁用递归语句的自动绑定变量窥探：
ALTER SESSION SET "_optim_peek_user_binds" = FALSE;—— 防止每次不同输入都生成新执行计划并写入 SYSAUX - 确保递归查询不带
FOR UPDATE或嵌套MERGE/INSERT—— 这些会强制分配 UNDO 段，且无法被undo_autotune及时回收
真正难处理的不是语法本身，而是递归逻辑与数据库资源管理机制的耦合点：临时段生命周期由会话控制，PGA 分配受隐式参数影响，而 SYSAUX 写入则取决于你是否开了某个“默认开启”的后台任务。每一步都要验证，而不是假设它“应该没事”。
