SHRINK使用举例

-- 使用shrink收缩表空间,shrink与move不同, move过程中会导致索引失效,shrink不会使索引失效.

-- 创建表

Create Table hxl.tb_hxl_list
 (
   Id Number,
   provcode Number
 )
 Partition By List(provcode)
 (
   Partition p_l1 Values(0),
   Partition p_l2 Values(1),
   Partition p_l3 Values(2),
   Partition p_l4 Values(3)
 );

 

-- 初始化数据

Declare
Begin
  For i In 1 .. 100000 Loop
     Insert Into hxl.tb_hxl_list Values(i,round(dbms_random.value(0,3)));
  End Loop;
  Commit;
  End;
/

-- 创建索引

Create Index hxl.IDX_TB_HXL_LIST_N1 On hxl.tb_hxl_list(Id) Global;

 

-- 查询目前表和索引的大小

Column Segment_Name format a20
Column Blocks format 9999999999
Column Extents format 9999999999
Column BYTES format 9999999999
Select Segment_Name,Blocks, Extents, a.BYTES
     From Dba_Segments a
 Where Segment_Name In( 'TB_HXL_LIST','IDX_TB_HXL_LIST_N1')
 Order By Segment_Name;
SEGMENT_NAME BLOCKS EXTENTS BYTES
-------------------- ----------- ----------- -----------

IDX_TB_HXL_LIST_N1 384 18 3145728
TB_HXL_LIST         72 9 589824
TB_HXL_LIST         32 4 262144
TB_HXL_LIST         40 5 327680
TB_HXL_LIST         72 9 589824

-- 删除数据

Delete From hxl.tb_hxl_list Where Rownum<=50000;

 

-- 查看目前表和索引占用空间大小，发现跟删除数据前没有变化

Select Segment_Name,Blocks, Extents, a.BYTES
     From Dba_Segments a
 Where Segment_Name In( 'TB_HXL_LIST','IDX_TB_HXL_LIST_N1')
 Order By Segment_Name;
SEGMENT_NAME BLOCKS EXTENTS BYTES
-------------------- ----------- ----------- -----------

IDX_TB_HXL_LIST_N1 384 18 3145728
TB_HXL_LIST         72 9 589824
TB_HXL_LIST         32 4 262144
TB_HXL_LIST         40 5 327680
TB_HXL_LIST         72 9 589824

 

-- 启用行迁移

ALTER TABLE TB_HXL_LIST ENABLE ROW MOVEMENT;

 

--使用compact选项收缩表

ALTER TABLE TB_HXL_LIST SHRINK SPACE COMPACT;

 

--查看目前表和索引占用空间大小,发现使用compact选项不能收缩表空间

Select Segment_Name,Blocks, Extents, a.BYTES
     From Dba_Segments a
 Where Segment_Name In( 'TB_HXL_LIST','IDX_TB_HXL_LIST_N1')
 Order By Segment_Name;
SEGMENT_NAME BLOCKS EXTENTS BYTES
-------------------- ----------- ----------- -----------

IDX_TB_HXL_LIST_N1 384 18 3145728
TB_HXL_LIST         72 9 589824
TB_HXL_LIST         32 4 262144
TB_HXL_LIST         40 5 327680
TB_HXL_LIST         72 9 589824

 

--使用 cascade选项收缩表 

alter table TB_HXL_LIST shrink space cascade;

 

-- 表和索引占用空间已经收缩

SQL> Select Segment_Name,Blocks, Extents, a.BYTES
  2 From Dba_Segments a
  3 Where Segment_Name In( 'TB_HXL_LIST','IDX_TB_HXL_LIST_N1')
  4 Order By Segment_Name;
SEGMENT_NAME BLOCKS EXTENTS BYTES
-------------------- ----------- ----------- -----------

IDX_TB_HXL_LIST_N1 160 17 1310720
TB_HXL_LIST         72 9 589824
TB_HXL_LIST          8 1 65536
TB_HXL_LIST          40 5 327680
TB_HXL_LIST          8 1 65536

使用shrink时有如下限制:

Restrictions on the shrink_clause
The shrink_clause is subject to the following restrictions:
* You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
* Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
* This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
* You cannot specify this clause for a compressed table.
* You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.