Oracle 高水位降低实战

适用版本：Oracle 10g / 11g / 12c / 19c（核心操作均适用，差异详见各步骤说明）
工作模式：普通表 / 分区表均适用；支持 Data Guard 环境（注意 nologging 限制）
核心场景：表空间使用率过高，业务侧完成历史数据 DELETE 后，高水位线未下降，需手动收缩段以释放已删除空间

一、环境说明

二、背景与原理

2.1 什么是高水位线

Oracle 段（Segment）以"高水位线（High Water Mark，HWM）"标记曾经使用过的最高块地址。全表扫描时，Oracle 需要扫描 HWM 以下的所有块，包含已被 DELETE 腾空的块，因此：

• DELETE 不降低 HWM：数据行被标记删除，但块仍属于该段，全表扫描代价不减
• TRUNCATE 直接重置 HWM：适用于清空全表，不可回滚
• MOVE / SHRINK 是在保留数据的前提下降低 HWM 的两种主要方式

2.2 降低高水位的两种核心方法

核心结论：

• 停机窗口充足 → 优先 MOVE，彻底消除碎片，效率更高
• 不停机 / 在线操作 → 优先 SHRINK，代价低，但需要开启 ROW MOVEMENT
• DG 强制日志（Force Logging）环境 → MOVE 可加 ONLINE；SHRINK 与 DG 兼容性更好，无 nologging 风险

三、方法一：MOVE 降低高水位（推荐用于停机窗口）

3.1 适用场景说明

• 表数据量大，碎片严重，需要彻底重排
• 存在可接受的停机窗口（Move 期间表不可 DML）
• Data Guard 环境：强制写入状态下不可用 NOLOGGING，需去掉该选项或使用 ONLINE 模式

⚠️ 重要提醒：

1. MOVE 会对表加排他锁，业务侧不可写入
2. MOVE 完成后，所有普通索引状态变为 UNUSABLE，必须立即 REBUILD
3. 索引 REBUILD 之前，走索引的 SQL 将报错或走全表扫描
4. Data Guard Force Logging 环境去掉 NOLOGGING；ONLINE 子句需 Oracle 12c+

3.1.1 普通表 — 批量 MOVE 操作流程

Step 1：确认目标表类型（确保为普通表，非 IOT/LOB 特殊表）

-- 查询目标表的 SEGMENT_TYPE，确认为 TABLE（非 TABLE PARTITION / IOT 等）
-- 本例中脱敏展示，实际替换为真实 OWNER 和表名

SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE owner = '[OWNER_A]'
  AND segment_name LIKE 'I_U%'
UNION ALL
SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE owner = '[OWNER_B]'
  AND segment_name LIKE 'I_DATA_I%HIS%'
UNION ALL
SELECT owner, segment_name, segment_type
FROM dba_segments
WHERE owner = '[OWNER_C]'
  AND segment_name LIKE 'CM_%';

Step 2：评估高水位及碎片率

-- 通过 dba_tables 统计信息与 dba_segments 实际段大小对比，计算碎片率
-- real_bytes_MB：按统计信息估算的实际数据量
-- seg_bytes_mb ：段在磁盘上的实际占用
-- GAP         ：二者之差即为碎片/高水位浪费的空间
-- frag_percent：碎片率（>30% 建议处理）

SELECT DISTINCT
       a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       ROUND(a.avg_row_len * a.num_rows / 1024 / 1024, 2)               AS real_bytes_mb,
       ROUND(b.seg_bytes_mb, 2)                                           AS seg_bytes_mb,
       ROUND(b.seg_bytes_mb - a.avg_row_len * a.num_rows / 1024 / 1024, 2) AS gap_mb,
       DECODE(a.num_rows, 0, '100%',
              (1 - ROUND(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb, 2)) * 100 || '%'
             )                                                             AS frag_percent
FROM   dba_tables a,
       (SELECT owner, segment_name, SUM(bytes / 1024 / 1024) seg_bytes_mb
        FROM dba_segments
        GROUP BY owner, segment_name) b,
       dba_segments c
WHERE  a.owner      = '[OWNER_A]'
  AND  a.table_name LIKE 'I_%'
  AND  a.table_name = b.segment_name
  AND  a.table_name = c.segment_name
  AND  a.owner      = b.owner
ORDER BY gap_mb DESC;

小贴士： num_rows 和 avg_row_len 来自统计信息，需提前收集过才准确。若统计信息过旧，可先执行 DBMS_STATS.GATHER_TABLE_STATS 再查。

Step 3：生成 MOVE 语句

-- 生成批量 MOVE 语句（开并行加速，ONLINE 子句需 12c+；11g 去掉 ONLINE）
-- 非 DG 或非 Force Logging 环境可追加 NOLOGGING 提升效率

SELECT 'ALTER TABLE ' || owner || '.' || table_name
       || ' MOVE PARALLEL 8;' AS sqltext
FROM   dba_tables
WHERE  table_name LIKE 'I_%'
   OR  table_name LIKE 'I_DATA_I%'
   OR  table_name LIKE 'CM_%';

版本差异说明：

• Oracle 11g：不支持 MOVE ... ONLINE，移表期间表完全不可 DML
• Oracle 12c Release 2（12.2）及以上：支持 MOVE ... ONLINE，Move 期间允许并发 DML（行锁粒度），且 Online Move 后索引自动维护，无需额外 REBUILD（分区表例外）
• DG Force Logging 环境：禁用 NOLOGGING，可加 ONLINE 或去掉 NOLOGGING 关键词

Step 4：执行 MOVE 语句（选择停机窗口执行）

将上一步生成的语句逐条或批量执行。执行期间监控表锁和 redo 产生量。

Step 5：检查索引状态 — MOVE 后索引必为 UNUSABLE

-- 查询 UNUSABLE 状态的索引（MOVE 后必须 REBUILD）
SELECT owner, index_name, table_name, status
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
  AND  table_name LIKE 'I_%';

Step 6：生成索引 REBUILD 语句并执行

-- 生成重建语句，ONLINE 表示重建期间不锁表（Oracle 8i+ 支持）
-- 开并行加速重建，重建后须关闭并行（见 Step 8）
SELECT 'ALTER INDEX ' || owner || '.' || index_name
       || ' REBUILD ONLINE PARALLEL 8;' AS sqltext
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
  AND  (table_name LIKE 'I_%'
    OR  table_name LIKE 'I_DATA_I%'
    OR  table_name LIKE 'CM_%');

⚠️ Unique Index 注意： 分区表上的 Global Unique Index 在 MOVE 分区后状态变 UNUSABLE；LOCAL INDEX 仅对应分区失效。

Step 7：验证索引状态

-- 确认所有目标表索引状态均为 VALID
SELECT owner, index_name, table_name, status
FROM   dba_indexes
WHERE  table_name LIKE 'CM_%'
  AND  status <> 'VALID';
-- 无行返回则表示所有索引已重建成功

Step 8：关闭表和索引的并行（生产环境必须执行）

-- 关闭表并行（并行开启后会影响后续所有查询的执行计划）
SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' PARALLEL 1;' AS sqltext
FROM   dba_tables
WHERE  table_name LIKE 'I_%'
   OR  table_name LIKE 'I_DATA_I%'
   OR  table_name LIKE 'CM_%';

-- 关闭索引并行
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOPARALLEL;' AS sqltext
FROM   dba_indexes
WHERE  table_name LIKE 'I_%'
   OR  table_name LIKE 'I_DATA_I%'
   OR  table_name LIKE 'CM_%';

Step 9：收集统计信息

-- MOVE 后表数据重排，旧统计信息失效，必须重新收集
-- ESTIMATE_PERCENT：采样比例，大表可用 10~30；DEGREE：并行度需与机器资源匹配

EXEC DBMS_STATS.GATHER_TABLE_STATS(
       ownname         => '[OWNER_C]',
       tabname         => 'CM_[TABLENAME]',
       estimate_percent => 30,
       method_opt       => 'FOR ALL COLUMNS SIZE REPEAT',
       degree           => 8,
       granularity      => 'ALL',
       cascade          => TRUE,
       no_invalidate    => FALSE
     );

3.1.2 批量评估碎片（大批量表的辅助方法）

当需要对某表空间下所有大表进行碎片评估时，可使用以下方法：

创建辅助表

-- 存放待评估的表名
CREATE TABLE system.temp_target_segments (
    segment_name  VARCHAR2(40),
    owner         VARCHAR2(20)
);

-- 存放碎片评估结果
CREATE TABLE system.frag_results (
    owner         VARCHAR2(20),
    tname         VARCHAR2(40),
    real_size_mb  NUMBER,
    seg_size_mb   NUMBER
);

插入待评估表名（超过 1GB 的大表）

INSERT INTO system.temp_target_segments
SELECT segment_name, owner
FROM   dba_segments
WHERE  segment_type     = 'TABLE'
  AND  owner            = '[OWNER_EDW]'
  AND  tablespace_name  = '[TABLESPACE_NAME]'
  AND  bytes / 1024 / 1024 / 1024 > 1;

COMMIT;

创建精确计算实际数据大小的函数

-- 利用 DBMS_SPACE.SPACE_USAGE 精确计算段的实际数据量（排除未格式化块和空闲块）
-- 参数：段名、OWNER、段类型（默认 TABLE）、分区名（分区表需传入）

CREATE OR REPLACE FUNCTION real_size(
    p_segname   IN VARCHAR2,
    p_owner     IN VARCHAR2 DEFAULT USER,
    p_type      IN VARCHAR2 DEFAULT 'TABLE',
    p_part_name IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER AUTHID CURRENT_USER AS
    -- DBMS_SPACE.SPACE_USAGE 返回的各类空闲块信息
    l_unformatted_blocks  NUMBER;  l_unformatted_bytes NUMBER;
    l_fs1_blocks NUMBER; l_fs1_bytes NUMBER;  -- 0~25% 可用
    l_fs2_blocks NUMBER; l_fs2_bytes NUMBER;  -- 25~50% 可用
    l_fs3_blocks NUMBER; l_fs3_bytes NUMBER;  -- 50~75% 可用
    l_fs4_blocks NUMBER; l_fs4_bytes NUMBER;  -- 75~100% 可用
    l_full_blocks NUMBER; l_full_bytes NUMBER;
    -- DBMS_SPACE.UNUSED_SPACE 返回的高水位以上未使用块信息
    l_total_blocks       NUMBER; l_total_bytes        NUMBER;
    l_unused_blocks      NUMBER; l_unused_bytes       NUMBER;
    l_lastusedextfileid  NUMBER; l_lastusedextblockid NUMBER;
    l_last_used_block    NUMBER;
    t_fs_bytes    NUMBER;
    t_total_bytes NUMBER;
BEGIN
    DBMS_SPACE.SPACE_USAGE(
        p_owner, p_segname, p_type,
        l_unformatted_blocks, l_unformatted_bytes,
        l_fs1_blocks, l_fs1_bytes,
        l_fs2_blocks, l_fs2_bytes,
        l_fs3_blocks, l_fs3_bytes,
        l_fs4_blocks, l_fs4_bytes,
        l_full_blocks, l_full_bytes,
        p_part_name
    );
    DBMS_SPACE.UNUSED_SPACE(
        p_owner, p_segname, p_type,
        l_total_blocks, l_total_bytes,
        l_unused_blocks, l_unused_bytes,
        l_lastusedextfileid, l_lastusedextblockid, l_last_used_block,
        p_part_name
    );
    -- 估算各空闲等级平均占用比，加上高水位以上未用空间，得到总空闲字节数
    t_fs_bytes    := l_fs1_bytes * 0.125
                   + l_fs2_bytes * 0.375
                   + l_fs3_bytes * 0.625
                   + l_fs4_bytes * 0.875
                   + l_unused_bytes;
    t_total_bytes := l_total_bytes;
    RETURN t_total_bytes - t_fs_bytes;  -- 返回实际数据量（字节）
EXCEPTION
    WHEN OTHERS THEN RETURN 1;
END;
/

版本差异说明：

• DBMS_SPACE.SPACE_USAGE 要求表空间必须为 ASSM（Automatic Segment Space Management） 模式，MSSM 模式不支持，调用会报 ORA-10614
• Oracle 10g+ 均支持此过程

批量写入碎片评估结果

-- 遍历 temp_target_segments，逐表计算实际大小并写入 frag_results
SPOOL frag_check.log
SET SERVEROUTPUT ON

DECLARE
    CURSOR c1 IS SELECT * FROM system.temp_target_segments;
    v_sql LONG;
BEGIN
    FOR i IN c1 LOOP
        v_sql := 'INSERT INTO system.frag_results
                  SELECT owner, segment_name,
                         TRUNC(real_size(''' || i.segment_name || ''','''
                               || i.owner || ''')/1024/1024, 2) real_size_mb,
                         bytes/1024/1024 seg_size_mb
                  FROM   dba_segments s
                  WHERE  s.owner = ''' || i.owner || '''
                    AND  s.segment_name = ''' || i.segment_name || '''';
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('处理：' || i.owner || '.' || i.segment_name);
        COMMIT;
    END LOOP;
END;
/

SPOOL OFF

查看碎片评估结果，筛选目标表

-- gap_mb 越大，收益越高；frag_ratio > 0.3（30%）建议 MOVE
SELECT owner,
       tname,
       real_size_mb,
       seg_size_mb,
       ROUND(seg_size_mb - real_size_mb, 2)              AS gap_mb,
       ROUND((seg_size_mb - real_size_mb) / seg_size_mb, 2) AS frag_ratio
FROM   system.frag_results
ORDER BY gap_mb DESC;

3.1.3 分区表 — MOVE PARTITION 操作流程

-- Step 1：批量评估分区碎片（同普通表，替换 segment_type='TABLE PARTITION'）
INSERT INTO system.temp_part_segments
SELECT segment_name, owner, partition_name
FROM   dba_segments
WHERE  segment_type    = 'TABLE PARTITION'
  AND  owner           = '[OWNER_EDW]'
  AND  tablespace_name = '[TABLESPACE_NAME]'
  AND  bytes / 1024 / 1024 / 1024 > 1;

COMMIT;

-- Step 2：生成 MOVE PARTITION 语句
SELECT 'ALTER TABLE ' || table_owner || '.' || table_name
       || ' MOVE PARTITION ' || partition_name
       || ' TABLESPACE [TABLESPACE_NAME];' AS sqltext
FROM   dba_tab_partitions
WHERE  table_owner   = '[OWNER_EDW]'
  AND  table_name    = '[TABLE_HIS]'
  AND  partition_name = '[P_YYYYMM]';

-- Step 3：查询失效的分区索引
SELECT index_owner, index_name, partition_name, status
FROM   dba_ind_partitions a
WHERE  index_name IN (
           SELECT index_name
           FROM   dba_part_indexes
           WHERE  table_name = '[TABLE_HIS]'
       )
  AND  index_owner = '[OWNER_EDW]'
  AND  status <> 'USABLE';

-- Step 4：生成重建分区索引语句
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name
       || ' REBUILD PARTITION ' || partition_name
       || ' TABLESPACE [TABLESPACE_NAME];' AS sqltext
FROM   dba_ind_partitions
WHERE  status <> 'USABLE'
  AND  index_owner = '[OWNER_EDW]';

-- Step 5：验证对象状态
SELECT object_name, object_type, status
FROM   dba_objects
WHERE  object_name = '[TABLE_HIS]'
  AND  owner       = '[OWNER_EDW]';

四、方法二：SHRINK SPACE 降低高水位（推荐用于在线/不停机场景）

4.1 前提条件

1. 表空间必须为 ASSM 模式（自动段空间管理）—— 使用手动段管理（MSSM）的表空间不支持 SHRINK
2. 开启 ROW MOVEMENT：SHRINK 会移动行数据，ROWID 会在段内短暂改变，需提前开启

⚠️ 注意：

• 含有 Function-Based Index（函数索引）、Domain Index（全文索引）、LOB 列 的表，SHRINK SPACE COMPACT 不直接支持（LOB 段需单独 SHRINK LOB）
• Bitmap Join Index 所在表不支持 SHRINK
• IOT（Index-Organized Table）不支持 SHRINK
• 行迁移（Row Migration）严重的表，SHRINK 前建议先 MOVE 处理

4.2 操作步骤

Step 1：开启 ROW MOVEMENT（必须，SHRINK 前执行）

-- 开启行移动，允许 SHRINK 在段内重新排布数据行
-- 可批量生成，然后逐条执行
SELECT 'ALTER TABLE ' || owner || '.' || table_name
       || ' ENABLE ROW MOVEMENT;' AS sqltext
FROM   dba_tables
WHERE  owner      = '[OWNER_A]'
  AND  table_name IN ('[TABLE1]', '[TABLE2]', '[TABLE3]');

Step 2：SHRINK SPACE COMPACT（第一阶段 — 数据整理）

-- COMPACT 阶段：仅整理数据块、合并空闲空间，不降低 HWM，不锁表
-- 耗时较长，可在业务高峰期执行；此阶段 DML 操作仍可正常进行

ALTER TABLE [OWNER_A].[TABLE1] SHRINK SPACE COMPACT;

Step 3：SHRINK SPACE（第二阶段 — 降低高水位）

-- 降低 HWM 并将空间释放给表空间
-- 此阶段短暂加排他锁（通常毫秒级），执行速度很快

ALTER TABLE [OWNER_A].[TABLE1] SHRINK SPACE;

一步完成法（小表或低峰期）：

ALTER TABLE [OWNER_A].[TABLE1] SHRINK SPACE CASCADE;
-- CASCADE：同时对该表的所有关联索引也执行 SHRINK，避免索引失效

Step 4：SHRINK 后无需 REBUILD 索引（CASCADE 已处理）

与 MOVE 不同，SHRINK 操作：

• 不使用 NOLOGGING 模式，redo 正常产生，DG 兼容
• CASCADE 子句自动维护普通 B-Tree 索引，索引不会变为 UNUSABLE
• ROWID 不发生外部语义变化（行在同一段内移动，外部引用不受影响）

Step 5：关闭 ROW MOVEMENT（操作完成后恢复默认，建议执行）

-- 关闭行移动（生产环境建议操作完成后关闭）
SELECT 'ALTER TABLE ' || owner || '.' || table_name
       || ' DISABLE ROW MOVEMENT;' AS sqltext
FROM   dba_tables
WHERE  owner      = '[OWNER_A]'
  AND  table_name IN ('[TABLE1]', '[TABLE2]', '[TABLE3]');

Step 6：收集统计信息

-- SHRINK 后统计信息同样需要刷新
EXEC DBMS_STATS.GATHER_TABLE_STATS(
       ownname         => '[OWNER_A]',
       tabname         => '[TABLE1]',
       estimate_percent => 20,
       method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
       degree           => 8,
       cascade          => TRUE,
       no_invalidate    => FALSE
     );

五、两种方案综合对比

5.1 选型建议

存在停机窗口
├── 是 → MOVE（可开高并行，彻底消除碎片，效率最高）
│        └── DG 强制日志 → 去掉 NOLOGGING，加 ONLINE（12.2+）
└── 否 → SHRINK SPACE CASCADE
          ├── 高峰期：先 COMPACT（整理数据，不降 HWM）
          └── 低峰期：再 SHRINK SPACE（瞬间降低 HWM）

六、完整操作 SOP 速查

6.1 MOVE 方案 SOP

1. 确认表类型（普通表/分区表/IOT）
2. 评估碎片率（GAP_MB > 500MB 或 frag_percent > 30% 建议处理）
3. 选择停机窗口（11g 必须；12.2+ 可 ONLINE 在线）
4. 执行 MOVE PARALLEL N（非 DG 可加 NOLOGGING）
5. 立即检查索引状态（dba_indexes.status = 'UNUSABLE'）
6. REBUILD ONLINE PARALLEL N 重建索引
7. 验证索引状态（status = 'VALID'）
8. 关闭表/索引并行（PARALLEL 1 / NOPARALLEL）
9. 收集统计信息

6.2 SHRINK 方案 SOP

1. 确认表空间为 ASSM 模式
2. ENABLE ROW MOVEMENT
3. SHRINK SPACE COMPACT（高峰期执行，仅整理数据）
4. SHRINK SPACE CASCADE（低峰期执行，降低 HWM 并维护索引）
5. DISABLE ROW MOVEMENT（恢复默认）
6. 收集统计信息

七、注意事项 & 常见问题

7.1 高水位评估注意

• dba_tables.num_rows 和 avg_row_len 依赖统计信息，未收集或过期时数值不准
• 建议在评估前先执行一次 DBMS_STATS.GATHER_TABLE_STATS(... ESTIMATE_PERCENT => 5)
• DBMS_SPACE.SPACE_USAGE 返回的是 ASSM 段的实际使用分布，精确度更高

7.2 MOVE 的常见问题

7.3 SHRINK 的常见问题

八、总结 & 注意事项

最佳实践建议：

• 优先 SHRINK：对于 7×24 系统，在高峰期运行 COMPACT 阶段，低峰期完成 HWM 调整，对业务影响最小
• MOVE 用于彻底整理：有停机窗口时使用，消除碎片效果更彻底，速度更快
• 两者可配合使用：先 SHRINK 释放部分空间满足 MOVE 的空间需求，再 MOVE 彻底整理