oracle合并表的碎片

Shrink space合并表的碎片
一般表里有碎片我们都采用alter table table_name move tablespace_name,或者exp,drop table table_name,imp的2种方式10G给我们其他的方法.下面我来试一吧

用Shrink Space收缩Oracle数据段
在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:/oracle/product/10.2.0/oradata/orcl/ts_auto.dbf' size 100m
 extent management local segment space management auto;

表空间已创建。

--建议测试表
SQL> create table  table_auto  as select * from dba_objects;

表已创建。

--查看shrink前的块数量
SQL> select blocks from dba_segments where segment_name='table_auto';

    BLOCKS                                                                    
----------                                                                    
       768                                                                   

--delete数据后，空间占用没有变化
SQL> delete from table_auto;

已删除50390行。

SQL> commit;

提交完成。

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

    BLOCKS                                                                    
----------                                                                    
       768                                                                    

--直接收缩，提示必须启动row movement选项
SQL> alter table table_auto shrink space;
alter table table_auto shrink space
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table table_auto enable row movement;

表已更改。

--收缩成功，空间已经释放
SQL> alter table table_auto shrink space;

表已更改。

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

    BLOCKS                                                                    
----------                                                                    
         8                                                                    

--shrink不能在segment space management manaual的表空间的段上执行
SQL> create tablespace ts_manual datafile 'd:/oracle/product/10.2.0/oradata/orcl/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