如何正确处理Oracle表高水位线

如何正确处理Oracle表高水位线

在Oracle数据库管理中，表的高水位线（High Water Mark，HWM）是一个关键概念。当表频繁删除大量数据时，高水位线不会自动下降，这会导致表空间膨胀和查询性能下降。本文将详细介绍如何检查和处理表的高水位线问题，包括多种实用的解决方案和技术细节。

在生产环境中，我们经常会遇到这种情况，表的数据量不大，但是表对应的表空间占的存储空间比较大，且查询速度也开始越来越慢，这是由于有些表频繁删除过大量数据，导致表已经处于高水位线，一直没有释放，表的高水位线只会随着数据的增加而上升，但不会因数据的删除而自动下降。即使你删除了表中的许多数据，表的高水位线仍然保持在原来的位置。最终会出现表空间的膨胀，数据库查询时会扫描一些已经不再有数据的空闲数据块，增加了 I/O 操作，从而影响查询性能。为了处理表出现高水位线问题，我们在日常维护中需要定期检查表的水位线，如果发现表出现高水位线，我们需要采取适当的措施来降低和管理表的高水位线，以下将介绍如何检查与处理表高水位线。

高水位线处理方法

针对表的高水位线处理，可以使用以下几种方式：

• 收缩表空间
• 重新创建表 create table xxx as
• 使用 alter table xxx move
• 如果表是大表，可以使用分区表，单独管理每个分区的高水位线
• 定期重建索引
• 使用 expdp 导出再重新导入

在处理高水位线前，我们需要先查出哪些表已经处理高水位线，检查表的高水位线（HWM）与实际使用的空间之间的差异。从查询结果中的高水位线（字段 hwm ）与表的平均已用数据块数（字段 waste_per ），可以看出哪些表是浪费空间最多的，这样我们可以针对特定的表进行处理，其中我们安装数据库时，数据库的块大小为 8K，所以除以 8192。

SELECT w.owner, w.table_name, tc.comments, hwm, avg_used_blocks,
       greatest(round(100 * (nvl(hwm - avg_used_blocks, 0) /
                       greatest(nvl(hwm, 1), 1)), 2), 0) waste_per,
       w.last_analyzed, w.num_rows
  FROM (SELECT a.owner, a.blocks - b.empty_blocks - 1 hwm, table_name,
                decode(round((b.avg_row_len * num_rows *
                              (1 + (pct_free / 100))) / 8192, 0), 0, 1,
                        round((b.avg_row_len * num_rows *
                               (1 + (pct_free / 100))) / 8192, 0)) + 2 avg_used_blocks,
                b.last_analyzed last_analyzed, b.num_rows
           FROM dba_segments a, dba_tables b
          WHERE a.owner = b.owner
            AND segment_name = table_name
            AND segment_type = 'TABLE' /*对象类型*/
         ) w
  JOIN dba_tab_comments tc
    ON tc.table_name = w.table_name
   AND tc.owner = w.owner
 ORDER BY waste_per DESC;

收缩表空间（推荐使用）

执行 alter table table_name shrink space，执行该指令之前必须允许行移动 alter table table_name enable row movement，否则会出同以下错误。

alter table 表名 enable row movement;
alter table 表名 shrink space;

执行行移动后，再次执行收缩表空间，可以看到表的高水位线已经降下来了从原来的 37887 降到 39，如上图，这通常在表的空间已被大量删除后使用，并且表的空间需要通过重组来进行优化。

重新创建表

可以使用 create table test_new as select * from test 方式，但使用这种方式如果原来已经存在表索引或者表存在默认值，这些都会不存在，需要重新创建或设置。

重新组织表

将数据移动到新的位置，从而重置高水位线。此操作会释放表中的空闲空间并重新设置高水位线。

alter table 表名 move;

💡执行 move 后，表中的所有数据将被移到新的数据块，可能会导致临时的性能下降和额外的 I/O 开销。

使用 expdp/impdp 导出再重新导入

如果涉及到整个用户的表空间里的大部分表，可以使用 expdp 命令先将数据导出来（可针对某个用户或者指定表空间导出数据）详细导出参数可使用命令 expdp -h 查看导出的详细参数。导出完成后可以删除原来的表空间再重新创建，之后再使用 impdp 命令重新导入。

总结

当 Oracle 表的高水位线过高时，会导致空间浪费、查询性能下降以及存储管理困难等问题。为了避免这些问题，我们需要定期维护表的高水位线，可以采用收缩表空间、重建表、重新组织表与导出再导入等方式来优化空间利用率并提高查询性能。

注意：truncate 命令会删除表中的所有数据并释放空间，但它 不会 降低表的高水位线。也就是说，虽然表中的数据被删除，表占用的数据块仍然存在，并且高水位线不会下降。