oracle 表碎片检查及整理 -m6米乐安卓版下载

很多时候，对于某张表有大量的 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 1000000
declare
p_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;
begin
dbms_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 = 0
fs2: blocks = 2
fs3: blocks = 0
fs4: blocks = 0
full 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');
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';

1. 显示当前用户可访问的所有分区表信息﹕
        all_part_tables
2. 显示当前用户所有分区表的信息﹕
        user_part_tables
3. 显示表分区信息 显示数据库所有分区表的详细分区信息﹕
        dba_tab_partitions
4. 显示当前用户可访问的所有分区表的详细分区信息﹕
        all_tab_partitions
5. 显示当前用户所有分区表的详细分区信息﹕
        user_tab_partitions
6. 显示子分区信息 显示数据库所有组合分区表的子分区信息﹕
        dba_tab_subpartitions
7. 显示当前用户可访问的所有组合分区表的子分区信息﹕
        all_tab_subpartitions
8. 显示当前用户所有组合分区表的子分区信息﹕
        user_tab_subpartitions
9. 显示分区列 显示数据库所有分区表的分区列信息﹕
        dba_part_key_columns
   10.显示当前用户可访问的所有分区表的分区列信息﹕
        all_part_key_columns
   11.显示当前用户所有分区表的分区列信息﹕
        user_part_key_columns
   12.显示子分区列 显示数据库所有分区表的子分区列信息﹕
        dba_subpart_key_columns
   13.显示当前用户可访问的所有分区表的子分区列信息﹕
        all_subpart_key_columns
   14.显示当前用户所有分区表的子分区列信息﹕
        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
————————————————————————————