Oracle 表空间高水位与空间回收实战：从 delete 后空间不释放到 SHRINK 与数据文件 RESIZE
一、具体的问题上周有位同行在群里问了一句：一张日志表按保留策略删了 4 亿行，dba_segments 里还是 420GB，一点没动；更奇怪的是删完之后那条 select count(*) 反而没变快，逻辑读跟删之前几乎一样。他已经在表上建了索引、也跑了统计信息，觉得"该做的都做了"。
这不是"删了但没提交"那种低级问题，也不是回收站没清。真实原因几乎总是同一个：DELETE 只把行标记成空闲，不动段的高水位线（HWM），而全表扫描读的是高水位线以下的所有块，包括那些已经空了、但还挂在段里的块。
我在 11.2 和 19c 上都反复验证过这件事：delete from t; commit; 之后 dba_segments.blocks 一个数都不会变，dba_tables.blocks（统计信息里的块数）要等重新收集统计信息才会变成接近 1，而这两者之间的差，就是被"浪费掉"的空间。代价是实打实的——备份要多备这 400GB 的脏块，RMAN 增量要扫它，全表扫描要多读几十万个块，count(*) 走索引还要读满整棵索引树。
所以这篇只解决一件事：已经 delete 掉的空间，怎么真正还给表空间，并且把数据文件也缩回去。
二、核心原理
先把三个层次分清楚，很多操作失误都来自把这三层混成一件事。
第一层：块内的空闲。 行被删除后，块内的空间被标记为空闲（ASSM 下体现为 FS1~FS4），后续 INSERT 可以复用。这一层是自动的，不需要任何人工动作。所谓"碎片"，在这一层其实不是问题。
第二层：段的高水位线（HWM）。 高水位线是段内曾经被格式化使用过的最高位置。全表扫描会从段头一直读到 HWM，HWM 以下哪怕整块都是空的，也要读。DELETE 永远不降 HWM，TRUNCATE 才降（它把 HWM 直接重置到段头之后），MOVE 和 SHRINK 也降。这就是"删了不释放"的全部秘密——不是空间没还，是没人去降这条线。
第三层：数据文件的块。 段的高水位线降下来之后，表空间里会出现大量连续空闲区，但数据文件本身不会自动缩小，dba_data_files.bytes 依然是那么大。要让操作系统真正拿回磁盘，还得 RESIZE 数据文件，而能不能缩、缩到多少，取决于这个文件里最高的那个块在哪。
还有一个容易被忽略的第四个对象：索引段和 LOB 段是独立的段，各有各的 HWM。 表 shrink 了，索引不会自己瘦；表 shrink 了，LOB 段也不会自己瘦（除非加 CASCADE 或者单独对 LOB 做 shrink）。大量日志表真正的空间黑洞往往就在 LOB 段上。
把这三层记住，后面所有命令就都能对上号了：SHRINK/MOVE 解决第二层，RESIZE 解决第三层，REBUILD/COALESCE 解决索引段的高水位线，CASCADE 和 MODIFY LOB 解决 LOB 段。
三、实例参考（动手步骤）
下面是一套可以直接照做的流程，示例表为 APP.T_ORDER_LOG，表空间 APP_DATA，环境 Oracle 19c（11.2 同样适用，差别我会标注）。
步骤 0：确认现状基线
-- 段级真实大小与 HWM 块数
SELECT segment_name, segment_type, blocks, bytes/1024/1024 AS mb, extents
FROM dba_segments
WHERE owner='APP' AND segment_name='T_ORDER_LOG';
-- 统计信息里的块数（滞后，但能看出优化器认为表有多大）
SELECT num_rows, blocks, avg_row_len
FROM dba_tables WHERE owner='APP' AND table_name='T_ORDER_LOG';
dba_segments.blocks 就是 HWM 对应的块数（乘 8KB 得到段占用的字节）。如果 blocks 是 5200 万、而 num_rows * avg_row_len / 8192 只有 300 万块，中间那 4900 万块就是空的。
再看精确的空闲分布（需要 ASSM，10g 以后默认就是）：
DECLARE
u_blk NUMBER; u_byt NUMBER;
f1b NUMBER; f1y NUMBER; f2b NUMBER; f2y NUMBER;
f3b NUMBER; f3y NUMBER; f4b NUMBER; f4y NUMBER;
fbNUMBER; fby NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE('APP','T_ORDER_LOG','TABLE',
u_blk,u_byt, f1b,f1y, f2b,f2y, f3b,f3y, f4b,f4y, fb,fby);
DBMS_OUTPUT.PUT_LINE('unformatted='||u_blk||' full='||fb||
' fs1='||f1b||' fs2='||f2b||' fs3='||f3b||' fs4='||f4b);
END;
/
unformatted 是 HWM 之上尚未格式化的块（这部分 shrink 能直接砍掉），fs1~fs4 是 HWM 之下的空闲块。FS1 是几乎空的块，FS4 是 75% 以上空的块。如果 unformatted 很大、fs3/fs4 很小，说明数据已经挪走了，属于最好处理的情况。
步骤 1：把索引膨胀也量化出来
SELECT index_name, leaf_blocks, distinct_keys, num_rows, blevel, clustering_factor
FROM dba_indexes WHERE table_name='T_ORDER_LOG' ORDER BY leaf_blocks DESC;
leaf_blocks 对比 num_rows 比例失衡（比如 12 万行占了 140 万个叶子块）就说明索引里也全是空叶子块。索引的 HWM 不能用 shrink 降到（ALTER INDEX ... SHRINK 不存在），只能 REBUILD 或 COALESCE。
步骤 2：SHRINK（在线，业务可继续写）
ALTER TABLE app.t_order_log ENABLE ROW MOVEMENT;
-- 第一阶段：只挪行，不降 HWM，可分多次执行，对业务最温和
ALTER TABLE app.t_order_log SHRINK SPACE COMPACT;
-- 第二阶段：降 HWM，并联动处理依赖的索引/LOB 段
ALTER TABLE app.t_order_log SHRINK SPACE CASCADE;
SHRINK 加的是行级锁，DML 期间也能跑，这是它比 MOVE 最大的优势。COMPACT 和完整的 SHRINK SPACE 可以分成两个低峰窗口执行，前者只搬行、后者才真正降线并占用较重的资源。
注意 CASCADE 才是关键——不加它，索引和 LOB 段纹丝不动。
步骤 3：需要重建段的场合用 MOVE ONLINE
当表上有函数索引、基于 ROWID 的物化视图，或者 SHRINK 报 ORA-10636 之类的不支持错误时，改用 MOVE：
-- 12c 起支持 ONLINE；12.2 起可用 UPDATE INDEXES 让索引保持可用
ALTER TABLE app.t_order_log MOVE ONLINE TABLESPACE app_data UPDATE INDEXES;
-- 11.2 没有 ONLINE，只能停业务窗口做，且索引会变 UNUSABLE
-- ALTER TABLE app.t_order_log MOVE TABLESPACE app_data;
-- 之后必须重建索引，否则查询报 ORA-01502
MOVE 是重建整个段，HWM 直接归位，代价是需要额外一份空间和更长的锁时间。能 SHRINK 就别优先用 MOVE。
步骤 4：索引单独处理
-- 彻底回收：重建（在线，需要额外空间）
ALTER INDEX app.idx_order_log_dt REBUILD ONLINE TABLESPACE app_idx;
-- 温和版：只合并同一分支内的空闲叶子块，段大小不会变小
ALTER INDEX app.idx_order_log_dt COALESCE;
COALESCE 常常被误当成"缩索引"，它只是把同一父节点下的空闲块合并，dba_segments.bytes 基本不变。要真降大小只有 REBUILD。
步骤 5：数据文件 RESIZE
先找这个文件里最高的块在哪，再决定能缩到多少：
SELECT file_id, MAX(block_id + blocks - 1) AS hwm_block
FROM dba_extents WHERE file_id = 7 GROUP BY file_id;
-- 结果假设 hwm_block = 2,560,000，则最小不能小于 2560000*8/1024 = 20000MB
SELECT file_id, bytes/1024/1024 AS mb, autoextensible FROM dba_data_files WHERE file_id = 7;
ALTER DATABASE DATAFILE 7 RESIZE 21000M;
留 5% 余量，别贴着 HWM 缩——差一个块就是 ORA-03297。
步骤 6：验证复查
-- 段是否真的瘦了
SELECT blocks, bytes/1024/1024 AS mb FROM dba_segments
WHERE owner='APP' AND segment_name='T_ORDER_LOG';
-- 全表扫描的逻辑读前后对比（关键指标）
SET AUTOTRACE ON STATISTICS
SELECT COUNT(*) FROM app.t_order_log;
-- 统计信息必须重新收，否则优化器还按 420GB 估成本
EXEC DBMS_STATS.GATHER_TABLE_STATS('APP','T_ORDER_LOG',CASCADE=>TRUE);
治理前后对比
指标治理前治理后变化
段大小 dba_segments420 GB86 GB-79.5%
段块数（HWM）52,100,00010,720,000-79.4%
全表扫描逻辑读4,180,000870,000-79.2%
count(*) 全扫耗时62 s11 s-82%
索引叶子块1,420,000310,000-78.2%
数据文件 7 大小500 GB210 GB-290 GB 归还 OS
每日全备耗时3 h 40 m52 m-76%
四、实操检查清单
[*]先用 dba_segments.blocks 减 dba_tables.blocks（或 num_rows*avg_row_len/8192）算出浪费量，确认值得动手再动。
[*]动 SHRINK 之前必须 ENABLE ROW MOVEMENT，否则直接 ORA-10636。
[*]一律用 SHRINK SPACE CASCADE，别只对表做，索引和 LOB 段要一起处理。
[*]确认表所在表空间是 ASSM（dba_tablespaces.segment_space_management = 'AUTO'），MSSM 表空间不支持 shrink。
[*]大表分两个窗口：先 SHRINK SPACE COMPACT，低峰再 SHRINK SPACE CASCADE。
[*]操作前确认 UNDO 表空间有足够余量（shrink 会产生大量 undo 和 redo），并在归档空间上留出余量。
[*]MOVE 之后立即检查 dba_indexes.status，有 UNUSABLE 的必须重建，否则线上直接报错。
[*]数据文件 RESIZE 目标值必须大于该文件最高块的结束位置，且留 5% 余量。
[*]收尾一定跑 DBMS_STATS.GATHER_TABLE_STATS，并复查执行计划（shrink 会让依赖游标失效、统计信息错位）。
[*]治理后一周内复看 dba_segments 与全备耗时，确认没有回涨到原位。
几个容易踩的坑
把 COALESCE 当 REBUILD 用。 索引做 COALESCE 之后查询可能略快，但段大小不变，磁盘和备份压力一点没减，很多人以为处理完了，其实还在。
只 shrink 表，忘了 LOB。 日志表常带 CLOB 报文体，LOB 段单独算 HWM。不加 CASCADE，表从 420GB 缩到 90GB，整体还是 380GB，白干一场。
MOVE 后忘了索引。 11.2 的 MOVE 会让索引全部 UNUSABLE，第二天早高峰所有相关查询报 ORA-01502。要么用 12.2+ 的 UPDATE INDEXES，要么把重建索引写进同一份操作脚本。
数据文件缩得太贴边。 按当前 HWM 精确缩，第二天插入一点数据就 ORA-03297。永远留余量。
用 shrink 替代分区设计。 真正的根治是让日志表按天/按月分区，用 DROP PARTITION 代替 DELETE——那是直接丢段的动作，不留 HWM 问题、不留 undo 压力。表已经 4 亿行还没分区的，先做在线重定义分区，再谈 shrink。
在业务高峰做全表 shrink。 即使 DML 不阻塞，shrink 期间的 IO 和 latch 竞争会把 P99 顶上去，日志表尤其明显。挑凌晨，并且用 COMPACT 分期。
最后一句实话：DELETE 之后空间不释放不是缺陷，是 Oracle 的设计——它假定你删完还会再插。真正的问题在于业务方把它当成了"归档"，而归档该用分区或 TRUNCATE。把这条讲给写 SQL 的人听，比事后 shrink 十次都管用。
页:
[1]