Oracle 表空间碎片整理

【转载】Oracle 表空间碎片整理  

2014-02-27 18:31:52|  分类： oracle|举报|字号 订阅

本文转载自小娜《Oracle 表空间碎片整理》

当生成一个数据库时，它会 分成称为表空间( Tablespace )的多个逻辑段( Segment )，如系统( System )表空间 , 临时( Temporary )表空间等。一个表空间可以包含多个数据范围( Extent )和一个或多个自由范围块，即自由空间( Free Space )。

当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的。当要满足一个空间要求时，数据库不再合并相邻的自由范围(除非别无选择)，而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。

从网上查了资料，整理表空间碎片的的方法主要有三种：

一是用alter table table_name move tablespace_name；alter index index_name rebuild；如果执行  ALTER TABLE xxx MOVE后还没有来的及执行  ALTER INDEX xxx REBUILD就有人对该表进行了查询或更新，则会报错，因为第一个操作会改变rowid并使索引失效。

二是用导入导出操作。个人认为这种方法的效果比较好。

三是用alter table table_name shrink space，后面有两个参数：cascade，compact。compact：这个参数是当系统负载比较大时使用，做次动作会减小性能的影响，在负载比较轻的时候做一次alter table table_name shrink space就可以了。 cascade：相当于rebuild index。收缩其实就是insert+delete，把数据搬到一起，所在数据的rowid也就变了，统计信息当然也变了，所以收缩后必须做analyze（如果你的sql引擎不是rule规则）。这种方法的速度比较慢，而且感觉效果不如前两者。

Oracle 收缩表大小 Oracle Shrink Table

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。

如果经常在表上执行DML操作，会造成数据库块中数据分布稀疏，浪费大量空间。同时也会影响全表扫描的性能，因为全表扫描需要访问更多的数据块。从oracle10g开始，表可以通过shrink来重组数据使数据分布更紧密，同时降低HWM释放空闲数据块。

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space语句两个阶段都执行。

shrink space compact只执行第一个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

注意：alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

============================================================================================

utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

0 - The level of parallelism is derived based on the CPU_COUNT parameter. 
1 - The recompilation is run serially, one object at a time. 
N - The recompilation is run in parallel with "N" number of threads. 
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

============================================================================================

语法：

alter table shrink space [ | compact | cascade ];

alter table shrink space compcat;

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table shrink space;

收缩表，降低 high water mark;

alter table shrink space cascade;

收缩表，降低 high water mark，并且相关索引也要收缩一下下。

alter index idxname shrink space;

回缩索引

1：普通表

Sql脚本，改脚本会生成相应的语句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

2：分区表的处理

进行shrink space时 发生ORA-10631错误.shrink space有一些限制.

在表上建有函数索引（包括全文索引）会失败。

Sql脚本，改脚本会生成相应的语句

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;

select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;

select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';

示例

在oracle中可以使用alter table table_name shrink space收缩表,使用shrink有两个前提条件:

1、表必须启用row movement

2、表段所在表空间的段空间管理(segment space management)必须为auto

实验如下：

--建立一个segment space management auto表空间

SQL> create tablespace ts_auto datafile 'd:\ts_auto.dbf' size 100m extent management local segment space management auto;

--建议测试表

SQL> create table tb_auto tablespace ts_auto as select * from dba_objects;

--查看shrink前的块数量

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

BLOCKS                                                                    

----------                                                                    

       768  

                                                                

--delete数据后，空间占用没有变化

SQL> delete from tb_auto;

已删除49823行。

SQL> commit;

提交完成。

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

BLOCKS                                                                    

    ----------                                                                    

       768

                                                                  

--直接收缩，提示必须启动row movement选项

SQL> alter table tb_auto shrink space;

alter table tb_auto shrink space

*

第 1 行出现错误:

ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table tb_auto enable row movement;

表已更改。

--收缩成功，空间已经释放

SQL> alter table tb_auto shrink space;

表已更改。

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

    BLOCKS                                                                    

----------                                                                    

         8                                                                    

--shrink不能在segment space management manaual的表空间的段上执行

SQL> create tablespace ts_manual datafile 'd:\ts_mannel.dbf' size 100m extent

management local segment space management manual;

表空间已创建。

SQL> select tablespace_name,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                SEGMEN                                         

------------------------------ ------                                         

SYSTEM                         MANUAL                                         

UNDOTBS1                       MANUAL                                         

SYSAUX                         AUTO                                           

TEMP                           MANUAL                                         

USERS                          AUTO                                           

EXAMPLE                        AUTO                                           

TS_AUTO                        AUTO                                           

TS_MANUAL                      MANUAL                                         

已选择8行。

SQL> create table tb_manual tablespace ts_manual as select * from dba_objects;

表已创建。

SQL> alter table tb_manual shrink space

2 ;

alter table tb_manual shrink space

*

第 1 行出现错误:

ORA-10635: Invalid segment or tablespace type

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wh62592855/archive/2009/11/25/4873493.aspx

alter table move跟shrink space的区别

都知道alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move 跟shrink space还是有区别的。
Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
也许很难理解吧，看测试就知道了。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;

Table created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS       INIT
---------- ---------- ---------- ----------
TEST               10       1280         10

SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST                0         1280
--TEST表初始分配了10M的空间，可以看到有10个EXTENTS，1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS，1280个空闲BLOCKS，即该10M空间内的BLOCK都还没被ORACLE”格式化”。

SQL> begin
2   for i in 1..100000 loop
3   insert into test values(i);
4   end loop;
5   end;
6   /

PL/SQL procedure successfully completed.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS
---------- ---------- ----------
TEST               10       1280

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST              186         1094
--插入10W条数据后，分配的空间仍不变，因为10个EXTENTS还没使用完。显示使用了186个BLOCKS，空闲1094个BLOCKS。这时候的186BLOCKS即是高水位线

SQL> delete from test where rownum<=50000;

50000 rows deleted.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS
---------- ---------- ----------
TEST               10       1280

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST              186         1094

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;

USED_BLOCKS
-----------
         77
--这边可以看到，删掉一半数据后，仍然显示使用了186个BLOCKS，高水位没变。但查询真正使用的BLOCK数只有77个。所以DELETE操作是不会改变HWM的

SQL> alter table test move;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST               81         1199
--MOVE之后,HWM降低了，空闲块也上去了

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS
---------- ---------- ----------
TEST               10       1280
--但是分配的空间并没有改变，仍然是1280个BLOCKS。下面看用SHRINK SPACE的方式

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS
---------- ---------- ----------
TEST                1         88

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST               81            7
--分配的空间已经降到最小，1个EXTENTS ，88个BLOCKS

所以MOVE并不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK)，也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是blow and above HWM操作。
至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前放掉的空间，无疑增加了操作。

注意：
1.不过用MOVE的方式也可以做到真正的压缩分配空间，只要指定STORAGE参数即可。

SQL> drop table test;

Table dropped.

SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;

Table created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS       INIT
---------- ---------- ---------- ----------
TEST               10       1280         10

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST                0         1280

SQL> alter table test move storage (initial 1m);

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';

SEGMENT_NA    EXTENTS     BLOCKS       INIT
---------- ---------- ---------- ----------
TEST              16        128          1

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST               0          128

2.使用move时，会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。
3.使用shrink space时，索引会自动维护。如果在业务繁忙时做压缩，可以先shrink space compact，来压缩数据而不移动HWM，等到不繁忙的时候再shrink space来移动HWM。
4.索引也是可以压缩的，压缩表时指定Shrink space cascade会同时压缩索引，也可以alter index xxx shrink space来压缩索引。
5.shrink space需要在表空间是自动段空间管理的，所以system表空间上的表无法shrink space。