Oracle 表碎片检查及整理方案
作者 | 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 ondeclarev_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;begindbms_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 a30col TABLE_NAME for a30col PARTITION_NAME for a30col TABLESPACE_NAME for a30select 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 1000col frag format 999999.99col 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
————————————————————————————