Oracle 表碎片检查及整理方案

2023-11-03

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 表碎片检查及整理方案，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！

很多时候，对于某张表有大量的 delete 删除操作，但是发现空间并未释放，这是高水位未下降的原因，何为“高水位线”大概就是定义为 Oracle 段中已使用和未使用空间之间的分界。大量删除操作会导致表的碎片过多，从而影响性能，检查表的碎片率一般使用存储过程 SPACE_USAGE 程序。下面详细介绍一下 SPACE_USAGE 程序。

SPACE_USAGE 程序

此过程有两个变体来显示空间使用情况。

程序的第一种形式显示了高水位线段下数据块的空间使用情况。您可以计算 LOB、LOB 分区和 LOB 子分区的使用率。这个过程只能用于使用自动段空间管理创建的表空间。位图块、段头和区段图块不在这个过程中计算。注意，此重载不能在 SECUREFILE LOB 上使用。

过程的第二种形式返回有关 SECUREFILE LOB 空间使用情况的信息。它将返回 LOB 段中所有 SECUREFILE LOB 所使用的块中的空间量。这个过程显示 LOB 列正在使用的空间、过期保留的已释放空间和未过期保留的已释放空间。注意，这种重载只能在SECUREFILE LOB 上使用。

对于 LOB 段，从返回的full_blocks块unformatted_blocks数实际上是 LOB 段的块数。

语法 1：

DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN  VARCHAR2,
   segment_name            IN  VARCHAR2,
   segment_type            IN  VARCHAR2,
   unformatted_blocks      OUT NUMBER,
   unformatted_bytes       OUT NUMBER,
   fs1_blocks              OUT NUMBER,
   fs1_bytes               OUT NUMBER,
   fs2_blocks              OUT NUMBER,
   fs2_bytes               OUT NUMBER,
   fs3_blocks              OUT NUMBER,
   fs3_bytes               OUT NUMBER,
   fs4_blocks              OUT NUMBER,
   fs4_bytes               OUT NUMBER,
   full_blocks             OUT NUMBER,
   full_bytes              OUT NUMBER,
   partition_name          IN  VARCHAR2 DEFAULT NULL);

语法 2：

DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN    VARCHAR2,
   segment_name            IN    VARCHAR2,
   segment_type            IN    VARCHAR2,
   segment_size_blocks     OUT   NUMBER,
   segment_size_bytes      OUT   NUMBER,
   used_blocks             OUT   NUMBER,
   used_bytes              OUT   NUMBER,
   expired_blocks          OUT   NUMBER,
   expired_bytes           OUT   NUMBER,
   unexpired_blocks        OUT   NUMBER,
   unexpired_bytes         OUT   NUMBER,
   partition_name          IN    VARCHAR2 DEFAULT NULL);

各字段含义：

普通表碎片查看示例

set serverout on size 1000000declarep_fs1_bytes number;p_fs2_bytes number;p_fs3_bytes number;p_fs4_bytes number;p_fs1_blocks number;p_fs2_blocks number;p_fs3_blocks number;p_fs4_blocks number;p_full_bytes number;p_full_blocks number;p_unformatted_bytes number;p_unformatted_blocks number;begindbms_space.space_usage(segment_owner => 'TEST',segment_name => 'ORIGINAL',segment_type => 'TABLE',fs1_bytes => p_fs1_bytes,fs1_blocks => p_fs1_blocks,fs2_bytes => p_fs2_bytes,fs2_blocks => p_fs2_blocks,fs3_bytes => p_fs3_bytes,fs3_blocks => p_fs3_blocks,fs4_bytes => p_fs4_bytes,fs4_blocks => p_fs4_blocks,full_bytes => p_full_bytes,full_blocks => p_full_blocks,unformatted_blocks => p_unformatted_blocks,unformatted_bytes => p_unformatted_bytes);dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);dbms_output.put_line('Full blocks = '||p_full_blocks);end;/FS1: blocks = 0FS2: blocks = 2FS3: blocks = 0FS4: blocks = 0Full blocks = 11

输出解释：FS1表明有 0 个数据块具有 0%-25% 的空闲空间，FS2 表明有 2 个数据块具有 25%-50% 的空闲空间，FS3 表明有 0 个数据块具有 50%-75% 的空闲空间，FS4 表明有 0 个数据块具有 75%-100% 的空闲空间，FULL 表明有 11 个满的数据块。

分区表碎片查看示例

--分区表碎片查看
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('TEST', 'ORIGINAL', 'TABLE PARTITION', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, 'PAR1'); --PAR1 指分区名
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

分区表相关视图

上面示例需要查询分区名，一般使用 DBA_TAB_PARTITIONS 视图如下 SQL 查看，以下收集了关于分区表的相关视图，有需要的可以看看。

col TABLE_OWNER for a30
col TABLE_NAME for a30
col PARTITION_NAME for a30
col TABLESPACE_NAME for a30
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER='TEST' and TABLE_NAME='T1';

显示当前用户可访问的所有分区表信息﹕
ALL_PART_TABLES
显示当前用户所有分区表的信息﹕
USER_PART_TABLES
显示表分区信息显示数据库所有分区表的详细分区信息﹕
DBA_TAB_PARTITIONS
显示当前用户可访问的所有分区表的详细分区信息﹕
ALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息﹕
USER_TAB_PARTITIONS
显示子分区信息显示数据库所有组合分区表的子分区信息﹕
DBA_TAB_SUBPARTITIONS
显示当前用户可访问的所有组合分区表的子分区信息﹕
ALL_TAB_SUBPARTITIONS
显示当前用户所有组合分区表的子分区信息﹕
USER_TAB_SUBPARTITIONS
显示分区列显示数据库所有分区表的分区列信息﹕
DBA_PART_KEY_COLUMNS
显示当前用户可访问的所有分区表的分区列信息﹕
ALL_PART_KEY_COLUMNS
显示当前用户所有分区表的分区列信息﹕
USER_PART_KEY_COLUMNS
显示子分区列显示数据库所有分区表的子分区列信息﹕
DBA_SUBPART_KEY_COLUMNS
显示当前用户可访问的所有分区表的子分区列信息﹕
ALL_SUBPART_KEY_COLUMNS
显示当前用户所有分区表的子分区列信息﹕
USER_SUBPART_KEY_COLUMNS

用户级别查看碎片

当检查的表比较多时，甚至是全库时，使用如下 SQL 查看。

set lines 200 pages 1000
col frag format 999999.99
col owner format a30;
col table_name format a30;
col frag for a20


select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
       round(b.seg_bytes_mb, 2) seg_bytes_mb,
       decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) || '%' 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
 where a.table_name = b.segment_name
   and a.owner = b.owner
   --and a.owner not in ('SYS','SYSTEM','OUTLN','DMSYS','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','OLAPSYS','ORDSYS','MDSYS','SYSMAN')
   and a.owner in ('TEST_JIEKE','PROD','SCOTT')
   --and a.table_name='T_ZDW_DOWN_SYNC_REC'
   and decode(a.num_rows,0,100,(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 / b.seg_bytes_mb,2)) * 100) > 50
 order by b.seg_bytes_mb desc;

表碎片整理

通过碎片整理来收缩空间，调整高水位线，方法比较简单，首先需要启用行移动，然后 alter table …… shrink space 即可完成，期间不会阻塞 DML 操作，可能时间会很久，建议业务低峰期间操作。

示例如下：

--启用行移动功能
alter table prod.T_DOWN_SYNC_REC enable row movement;


--收缩表
alter table prod.T_DOWN_SYNC_REC shrink space cascade;
--cascade 可以收缩与索引段相关的空间


--禁用行移动功能
alter table prod.T_DOWN_SYNC_REC disable row movement;

除了使用 shrink space 外，还有截断表，移动表，导入导出。但是 truncate 表直接清理数据，一般情况下不可取；alter table t move;移动表会使索引失效，移动完需要 rebuild 重新建索引，移动表时对数据行的 rowid 有所变更，而索引中又包含了 rowid, 故 move 表会使索引失效。最后导出导入数据时，不能有新数据进入，这点也不太友好，除非停止业务，故此推荐 shrink space 。

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，你关心谁就分享给谁，一起学习共同进步~~~

❤️ 欢迎关注我的公众号，来一起玩耍吧！！！

————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————