使用dbms_space.space_usage来排查段空间问题
一个常见的空间问题：历史数据按期清理，DELETE 执行了几十万行，几个月后看 DBA_SEGMENTS ，段的大小纹丝不动；依赖这张表的全表扫描，耗时也没有任何改善。原因在于段的高水位标记（HWM）只升不降——数据删除后块空了，但仍然属于这个段，全表扫描照样要走过它们。
DBA_SEGMENTS 只能看到段占了多少空间，看不到这些块的"实心程度"。要看清 HWM 之下到底有多少满块、多少空块，需要用 DBMS_SPACE 包的 SPACE_USAGE 过程。该包的 EXECUTE 权限授予了 PUBLIC，普通用户可以直接调用。
一、块空间有哪些分类
SPACE_USAGE
把 HWM 之下的数据块按空闲空间比例分成六档：
| 输出参数 | 含义 |
|---|---|
| unformatted_blocks | HWM 之下尚未格式化的块（ASSM 直接路径写入留下的未格式化区域） |
| fs1_blocks | 空闲空间 0–25% 的块 |
| fs2_blocks | 空闲空间 25–50% 的块 |
| fs3_blocks | 空闲空间 50–75% 的块 |
| fs4_blocks | 空闲空间 75–100% 的块 |
| full_blocks | 已满的块 |
判断段健康程度的基本思路：大量数据被删掉之后，fs3、fs4 块显著增多而段大小不变，就印证了"空间空转"的判断；此时全表扫描的成本仍按 HWM 计算，收缩段（SHRINK 或 MOVE）才能把时间买回来。
需要特别注意一个前提：这组输出只适用于 ASSM（自动段空间管理）表空间中的段，即 DBA_TABLESPACES 的 SEGMENT_SPACE_MANAGEMENT 为 AUTO。手工空间管理的段会报 ORA-10614 之类的错误，只能改用同包的 UNUSED_SPACE 查看段的未用空间与最后一次使用的位置。
过程签名如下：
DBMS_SPACE .SPACE_USAGE (
segment_owner IN VARCHAR2 ,
segment_name IN VARCHAR2 ,
segment_type IN VARCHAR2 ,
unformatted_blocks OUT NUMBER ,
fs1_blocks OUT NUMBER ,
fs2_blocks OUT NUMBER ,
fs3_blocks OUT NUMBER ,
fs4_blocks OUT NUMBER ,
full_blocks OUT NUMBER ,
partition_name IN VARCHAR2 DEFAULT NULL ) ;
支持的段类型包括 TABLE、INDEX、CLUSTER、LOB 及其分区子分区。同名的另一个重载专门用于 SecureFile LOB 段，输出的是已用、过期、未过期的 chunk 数，不与本文的场景混用。
二、高水位线上涨的过程
1. 确认表空间为 ASSM（DBA）
SQL > select segment_space_management from dba_tablespaces
2 where tablespace_name = 'USERS' ;
SEGMENT_SPACE_MANAGEMENT
------------------------
AUTO
2. 准备数据（TEST 用户）
SQL > create table t_space (id number , pad varchar2 ( 1000 )) ;
Table created.
SQL > begin
2 for i in 1 .. 20000 loop
3 insert into t_space values (i , rpad ( 'x' , 1000 , 'x' )) ;
4 end loop ;
5 commit ;
6 end ;
7 /
PL/SQL procedure successfully completed.
3. 查看灌满后的块分布
SQL > set serveroutput on
SQL > declare
2 v_unfmt number ; v_fs1 number ; v_fs2 number ;
3 v_fs3 number ; v_fs4 number ; v_full number ;
4 v_dummy number ;
5 begin
6 dbms_space .space_usage (
7 segment_owner => user ,
8 segment_name => 'T_SPACE' ,
9 segment_type => 'TABLE' ,
10 unformatted_blocks => v_unfmt , unformatted_bytes => v_dummy ,
11 fs1_blocks => v_fs1 , fs1_bytes => v_dummy ,
12 fs2_blocks => v_fs2 , fs2_bytes => v_dummy ,
13 fs3_blocks => v_fs3 , fs3_bytes => v_dummy ,
14 fs4_blocks => v_fs4 , fs4_bytes => v_dummy ,
15 full_blocks => v_full , full_bytes => v_dummy ) ;
16 dbms_output .put_line ( 'unfmt=' || v_unfmt || ' fs1=' || v_fs1 ||
17 ' fs2=' || v_fs2 || ' fs3=' || v_fs3 ||
18 ' fs4=' || v_fs4 || ' full=' || v_full ) ;
19 end ;
20 /
unfmt = 1 fs1 = 6 fs2 = 5 fs3 = 0 fs4 = 0 full = 3198
PL/SQL procedure successfully completed.
新灌入的数据块几乎全部是满块（full=3198），符合预期。
4. 删除九成数据，再看一遍
SQL > delete from t_space where mod (id , 10 ) != 0 ;
18000 rows deleted.
SQL > commit ;
重复执行第 3 步的匿名块：
unfmt=1 fs1=5 fs2=8 fs3=150 fs4=2880 full=162
变化非常直观：原来 3198 个满块中的绝大多数变成了 fs4（空闲 75% 以上）块。结合段大小确认一下"空间没有归还"：
SQL > select bytes/1024/1024 mb from dba_segments
2 where owner = 'TEST' and segment_name = 'T_SPACE' ;
MB
----------
26
段仍然是 26MB。也就是说这 26MB 里真正的有效数据只剩约一成，但 HWM 没动，全表扫描仍要读完这 3300 多个块。
5. 收缩段，验证效果
SQL > alter table t_space enable row movement ;
Table altered.
SQL > alter table t_space shrink space ;
Table altered.
再执行第 3 步的匿名块和段大小查询：
unfmt=0 fs1=4 fs2=3 fs3=0 fs4=0 full=326
MB
----------
3
收缩后段只剩 3MB，块重新以满块为主，HWM 降到数据实际需要的位置，扫描成本同步下降。整个过程"删除后 fs4 堆积 → 收缩后恢复"的前后对比，就是 SPACE_USAGE 最大的实用价值：让"要不要收缩、收缩能收回多少"从经验判断变成数据判断。
tips：
-
该过程仅支持 ASSM 段；非 ASSM 段使用时会报错，应改用 DBMS_SPACE.UNUSED_SPACE，它可以输出段的总块数、HWM 之上的未用块数，以及最后一个使用块的位置，对判断表经过 MOVE 后能省多少空间同样有用；
-
输出不含段头、位图块等管理块，统计的是 HWM 之下的数据块；
-
对 LOB 段调用时，full_blocks、unformatted_blocks 返回的实际是 chunk 数，不是块数，估算大小时要按 chunk 尺寸换算；
-
分区表传入 partition_name，段类型相应使用 TABLE PARTITION 或 INDEX PARTITION；
-
SHRINK 之前需要 ENABLE ROW MOVEMENT，且 SHRINK 会移动行、更新索引；如果不能接受锁与行迁移的开销，也可以选择在业务低峰做 ALTER TABLE MOVE，但 MOVE 会导致索引失效需要重建，两者要按维护窗口权衡。
空间问题排查中，DBA_SEGMENTS 负责占了多少，DBMS_SPACE.SPACE_USAGE 负责占得值不值。两个数字一对照，删数据不还空间、扫描越来越慢这类问题，原因和处理量就都摆在了明面上。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70013625/viewspace-3182680/ ，如需转载，请注明出处，否则将追究法律责任。
