Oracle10g用Shrink Space收縮Oracle數(shù)據(jù)段

貝殼shell

在oracle中可以使用alter table table_name shrink space收縮表,使用shrink有兩個(gè)前提條件:
  1、表必須啟用row movement
  2、表段所在表空間的段空間管理(segment space management)必須為auto
實(shí)驗(yàn)如下：
--建立一個(gè)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;
表空間已創(chuàng)建。
--建議測(cè)試表
SQL> create table  tb_auto  as select * from dba_objects;
表已創(chuàng)建。
--查看shrink前的塊數(shù)量
SQL> select blocks from dba_segments where segment_name='TB_AUTO';
    BLOCKS                                                                    
----------                                                                    
       768                                                                   
--delete數(shù)據(jù)后，空間占用沒有變化
SQL> delete from tb_auto;
已刪除50390行。

SQL> commit;
提交完成。
SQL> select blocks from dba_segments where segment_name='TB_AUTO';
    BLOCKS                                                                    
----------                                                                    
       768                                                                    
--直接收縮，提示必須啟動(dòng)row movement選項(xiàng)
SQL> alter table tb_auto shrink space;
alter table tb_auto shrink space
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table tb_auto enable row movement;
表已更改。
--收縮成功，空間已經(jīng)釋放
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的表空間的段上執(zhí)行
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;
表空間已創(chuàng)建。
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;
表已創(chuàng)建。
SQL> alter table tb_manual  shrink space
  2  ;
alter table tb_manual  shrink space
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-10635: Invalid segment or tablespace type

段縮小操作只能應(yīng)用于自動(dòng)段空間管理的表空間，在對(duì)一個(gè)表空間中的表段進(jìn)行縮小操作時(shí)，必須為表啟動(dòng)記錄轉(zhuǎn)移，操作語法為：
SQL>alter table <table_name> enable row movement;
SQL>alter table <table_name> shrink space cascade;
SQL>alter table <table_name> modify lob(resume) (shrink space);
SQL>alter table <table_name> overflow shrink space;

SQL>analyze table table_name validate structure cascade;  --分析一下表及索引的一致性是否完好
SQL>analyze index index_name validate structure;  --驗(yàn)證數(shù)據(jù)塊及索引鍵的完整性
SQL>analyze table table_name validate structure;  --驗(yàn)證每個(gè)數(shù)據(jù)塊和每條記錄的完整性

SQL>alter table employees shrink space compact;
SQL>alter table employees shrink space;