本文共 8753 字，大约阅读时间需要 29 分钟。

数据库中表不断的insert，delete，update，导致表和索引出现碎片。这会导致HWM之前有很多的空闲空间，而oracle在做全表扫描的时候会读取HWM一下的所有块，这样会产生更多的IO，影响性能。

oracle提供了shrink space碎片整理的功能，对于索引要采取rebuild online的方式进行碎片整理。

高水位的管理机制

高水位的管理机制在 MSSM 和 ASSM 中不同，在以往的手动段空间管理中（MSSM），高水位标记 HWM，一个段分成三部分，header block，used block(row data)，unusedblock，其中 used block 和 unused block 之间的分界线就是高水位标记 HWM，当进行全表扫描的时候，会扫描到 HWM 下的所有数据块，即使 used block 中很多数据被删除了，全表扫描还是以HWM为准。

在自动段管理（ASSM）中，利用位图来代替空闲列表，当会话向表插入数据时，数据库只格式一个单独的位图块，而不是像 MSSM 中那样，会预先格式化一组块。在ASSM表空间中，除了一个 HWM 外，还有一个低 HWM。在 MSSM 中，HWM 推进时，所有的块都会格式化并立即生效，这样 Oracle 就可以安全的读取这些块。但是对于 ASSM，当 HWM推进时，Oracle 并不会立即格式所有的块，只是在第一次使用的时候才会对这些块进行格式化。也就是说，在第一次使用的的时候，即进行 insert 操作时，数据会插入到块中的任意水位线，位于低水位线（LHMW）和高水位线（HHMW）之间。因此在这个区域的许多块就不会被格式化。

背景

生产中这S_OPERATELOG, S_T_RTNRP_STATUS, S_T_SEND_REPORT三张表实际使用量不大（即truncate分区后），表空间数据文件还是占用很高，几个T，现需要释放不用的空间。

生产中遇到的案例一：

以下是生产中的三张表，且都是按天生成的分区表（不足一个月的数据，一年数据量很大）

select segment_name,round(sum(bytes / 1024 / 1024 / 1024), 2) G

  from user_segments

 where segment_name in

       ('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT') group by segment_name;

可以看出使用的并不是很大。

查看表使用的表空间

select owner, table_name, tablespace_name  from dba_tables where owner = 'SMART'   AND TABLE_NAME in       ('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT')UNIONselect TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME  from dba_Tab_Partitions where TABLE_OWNER = 'SMART'   AND TABLE_NAME in       ('S_OPERATELOG', 'S_T_RTNRP_STATUS', 'S_T_SEND_REPORT');

查看表空间使用情况：

select tablespace_name,

ROUND(sum(bytes / 1024 / 1024 / 1024),2) G，ROUND(sum(maxbytes / 1024 / 1024 / 1024),2) max_G

  from dba_data_files

 where file_name like '+FDATADG%'

 GROUP BY TABLESPACE_NAME ORDER BY 2 DESC;

可以看出几个表空间占用空间很大，尤其是前三个表空间。

查看表空间数据文件情况：

SELECT a.tablespace_name,       a.file_name,       round(a.bytes / 1024 / 1024 / 1024, 2) AS "current_bytes(GB)",       round(a.bytes / 1024 / 1024 / 1024 -             b.resize_to / 1024 / 1024 / 1024,             2) AS "shrink_by_bytes(GB)",       round(b.resize_to / 1024 / 1024 / 1024, 2) AS "resize_to_bytes(GB)"  FROM dba_data_files a,       (SELECT file_id,               MAX((block_id + blocks - 1) *                   (select value                      from v$parameter                     where name = 'db_block_size')) AS resize_to          FROM dba_extents         GROUP by file_id) b WHERE a.file_id = b.file_id      and a.TABLESPACE_NAME in ('SMART_OPLOG01',                             'SMART_NRRPSTA01',                             'SMART_NRRPSTA02',                             'SMART_NSNRP01',                             'SMART_NSNRP02') ORDER BY a.tablespace_name, a.file_name;

后三列表示当前占用大小，能够收缩大小,最小的resize大小。

处理办法：

1)    Shrink对应的表

注意：由于在线上，不能进行有表锁的操作，所以我并没有采用这种办法

alter table TABLE_NAME enable ROW MOVEMENT;--启动行移动功能

alter table TABLE_NAME shrink space compact;  --只整理碎片 不回收空间，业务繁忙的时候执行，等待时间会很久

-- 重置高水位，此时不能有DML操作

--alter table TABLE_NAME shrink space; --整理碎片并回收空间，并调整水位线，业务少时执行

ALTER TABLE TABLE_NAME  SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下   

alter table TABLE_NAME disable ROW MOVEMENT;--关闭行移动

2)    Resize 数据文件

select  ' alter database datafile '''||file_name ||''' resize 43g;' from dba_data_files where tablespace_name

   in (

   'SMART_NRRPSTA01'

   );

收缩前表空间使用如下：

收缩后表空间使用如下：

磁盘组使用如下：

原+FDATADG磁盘中使用达到96%,释放了20%的空间。

案例二 （move 与shrink space区别和注意事项）

都知道alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move跟shrink space还是有区别的。

下面看看实验：

move 实验：

insert into test1 SELECT rownum,dbms_random.string('a',200) from dual connect by rownum <=1000000;

commit;

delete test1 where empno>10 and empno<900000;

现在进行move 操作：

select b.TABLE_NAME,b.TABLESPACE_NAME from user_tables b where b.TABLE_NAME='TEST1';

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST1';

alter table test1 move tablespace users;

SELECT

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST1';

 alter index SYS_C008190 rebuild online;

可以看出move test1表操作后，索引失效（需要重建），move操作可以收缩段，消除空间碎片（delete 操作），使数据更紧密，分配的表段和dba_extents减少；

shirnk 实验：

create table test2( empno number primary key,ename varchar2(200)) storage (initial 1m next 2m pctincrease 0) pctfree 5 pctused 40;

insert into test2 SELECT rownum,dbms_random.string('a',200) from dual connect by rownum <=1000000;

commit;

 delete test2 where empno>10 and empno<900000;

commit;

 现在进行shrink操作：

alter table test2 enable ROW MOVEMENT;--启动行移动功能

alter table test2 shrink space compact; --只整理碎片 不回收空间

ALTER TABLE test2 SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下

alter table test2 disable ROW MOVEMENT;--关闭行移动

SELECT

 可以看出shrink test2表操作后，也可以收缩段，消除空间碎片（delete 操作），使数据更紧密，分配的表段和dba_extents减少；

再次交叉操作：

TEST1表进行move操作，TEST2表进行shrink操作：

现在把test1表进行shrink,test2表进行move  在看他们的结果。 

alter table test1 enable ROW MOVEMENT;--启动行移动功能

alter table test1 shrink space compact; --只整理碎片 不回收空间，业务繁忙的时候

ALTER TABLE test1 SHRINK SPACE CASCADE; --收缩表，降低高水位线，并且相关索引也要收缩一下

alter table test1 disable ROW MOVEMENT;--关闭行移动

select 'alter index '||A.INDEX_NAME||' rebuild online;',A.status from user_indexes a where a.TABLE_NAME='TEST2';

SELECT

 表的结果发生了神奇的变化，哈哈，对比一下：

select 'test1_'||count(*) as total from test1

test1是进行move后shrink操作；

test2是进行shrink后move操作；

原本move 表test1是23MB，shrink后变成22.125MB;

原本shrink表test2是22.235MB,但是move之后，增加到23MB；

他们的表结构以及记录数一样，插入是随机的值。

原因：

这是由于两张表申请的空间是23M；move,shrink 都会移动高水位，从delete后的200多MB，移动到20多MB;

但是move不会释放申请的空间（shrink会释放申请的空间），所以tmove 表test1是23MB，shrink后变成22.125MB;

而原本shrink表test2是22.235MB,但是move之后，增加到23MB（还是到申请的空间23MB）；

结论：

Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作；

其他注意事项说明如下：

使用move的一些注意事项：

b.move时对table的锁定

c.关于move时空间使用的问题：

Move解决问题：

Shrink space语法：

用shrink有两个前提条件:

segment shrink分为两个阶段：

生产中遇到的案例三（表空间碎片）

转载地址：http://uqouz.baihongyu.com/