Oracle10g - shrink

在 Oracle10g 中，使用 shrink 可以手工回收表、索引组织表（IOT）、索引、分区、子分区、物化视图，或者物化视图日志的存储空间。当然，段所在的表空间必须是自动段空间管理的。默认情况下，Oracle 会将段中的数据移动到前端，调整高水位，然后释放空间。

 

移动段中数据是需要 row movement 权限的，所以应提前设置你要 shrink 的对象 enable row movement。如果你的系统使用了基于 rowid 的触发器，也应该提前设置它为 disable 状态。

 

shrink 有两个参数：COMPACT 和 CASCADE。

 

COMPACT

如果你指定 COMPACT 参数，那么 Oracle 只会整理段中的数据，将数据移动到段的前端，而不会调整高水位线，所以也不会释放空间。等数据整理完毕后，你得再次使用 alter table ... shrink space 语句来释放空间。如果你想使用两个占用时间短的步骤，而不是一个长时间的步骤，可以考虑分成两步完成 shrink。

For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not wish to release the unused space, then you can use the appropriate COALESCE clause.

CASCADE

If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.

Restrictions on the shrink_clause

• You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

• Segment shrink is not supported for LOB segments even if CASCADE is specified.

• Segment shrink is not supported for tables with function-based indexes.

• This clause does not shrink mapping tables or overflow segments of index-organized tables, even if you specify CASCADE.

• 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.

下面我们测试一下：

 

0. 创建测试表：

 

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_yct as select * from dba_objects;

 

表已创建。

SQL> insert into test_yct select * from test_yct;

 

已创建49870行。

 

SQL> /

 

已创建99740行。

 

SQL> /

 

已创建199480行。

 

SQL> /

 

已创建398960行。

 

SQL> commit;

 

提交完成。

 

1. 查询表 test_yct 当前的状态

 

-- 数据量

SQL> select count(*) from test_yct;

 

  COUNT(*)
----------
    797920

 

-- 占用空间（M）

SQL> select bytes/1024/1024 from user_segments where segment_name='TEST_YCT';

 

BYTES/1024/1024
---------------
             88

 

-- 占用 extent

SQL> set pagesize 1000
SQL> select EXTENT_ID,BYTES,BLOCKS from user_extents where segment_name='TEST_YCT';

 

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128
        27    1048576        128
        28    1048576        128
        29    1048576        128
        30    1048576        128
        31    1048576        128
        32    1048576        128
        33    1048576        128
        34    1048576        128
        35    1048576        128
        36    1048576        128
        37    1048576        128
        38    1048576        128
        39    1048576        128
        40    1048576        128
        41    1048576        128
        42    1048576        128
        43    1048576        128
        44    1048576        128
        45    1048576        128
        46    1048576        128
        47    1048576        128
        48    1048576        128
        49    1048576        128
        50    1048576        128
        51    1048576        128
        52    1048576        128
        53    1048576        128
        54    1048576        128
        55    1048576        128
        56    1048576        128
        57    1048576        128
        58    1048576        128
        59    1048576        128
        60    1048576        128
        61    1048576        128
        62    1048576        128
        63    1048576        128
        64    1048576        128
        65    1048576        128
        66    1048576        128
        67    1048576        128
        68    1048576        128
        69    1048576        128
        70    1048576        128
        71    1048576        128
        72    1048576        128
        73    1048576        128
        74    1048576        128
        75    1048576        128
        76    1048576        128
        77    1048576        128
        78    1048576        128
        79    8388608       1024
        80    8388608       1024
        81    8388608       1024

已选择82行。

 

 

查询此时段中的高水位：

 

-- 查询头块

SQL> select header_file,header_block from dba_segments where segment_name='TEST_YCT';

 

HEADER_FILE HEADER_BLOCK
----------- ------------
          4        63667

 

SQL> alter system dump datafile 4 block 63667;

 

系统已更改。

 

SQL> select p.value||'\'||i.instance_name||'_ora_'||p.spid||'.trc' trace_path
  2  from v$process p, v$instance i, v$parameter p
  3  where p.addr = (
  4  select paddr from v$session
  5  where sid = ( select sid from v$mystat where rownum = 1)
  6  )
  7  and p.name='user_dump_dest';

 

TRACE_PATH
------------------------------------------------------------------------------
D:\ORACLE\ADMIN\ORCL\UDUMP\orcl_ora_3452.trc

 

头块中的部分信息：

 

...

  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 82     #blocks: 11264
                  last map  0x00000000  #maps: 0      offset: 2716 
      Highwater::  0x01012f89  ext#: 81     blk#: 1024   ext size: 1024 
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 11137
  mapblk  0x00000000  offset: 81   
                   Unlocked
  --------------------------------------------------------

...

 

目前高水位数据块为：

 

SQL> select to_number('12f89', 'xxxxxxx') from dual;

 

TO_NUMBER('12F89','XXXXXXX')
----------------------------
                       77705

 

 

2. 查询表 test_shrink 删除数据后的状态

 

 

-- 删除部分数据

SQL> delete test_yct where rownum < 700000;

 

已删除699999行。

 

SQL> commit;

 

提交完成。

 

-- 数据量

SQL> select count(*) from test_shrink;

 

  COUNT(*)
----------
     97921

 

-- 占用空间（M）

SQL> select bytes/1024/1024 from user_segments where segment_name='TEST_YCT';

 

BYTES/1024/1024
---------------
             88

 

-- 占用 extent
SQL> select EXTENT_ID,BYTES,BLOCKS from user_extents where segment_name='TEST_YCT';

 

... （与原来相同）

 

已选择82行。

 

查询此时段中的高水位：

 

-- 查询头块

SQL> select header_file,header_block from dba_segments where segment_name='TEST_YCT';

 

HEADER_FILE HEADER_BLOCK
----------- ------------
          4        63667

SQL> alter system dump datafile 4 block 63667;

 

系统已更改。

 

SQL> select p.value||'\'||i.instance_name||'_ora_'||p.spid||'.trc' trace_path
  2  from v$process p, v$instance i, v$parameter p
  3  where p.addr = (
  4  select paddr from v$session
  5  where sid = ( select sid from v$mystat where rownum = 1)
  6  )
  7  and p.name='user_dump_dest';

 

TRACE_PATH
------------------------------------------------------------------------------
D:\ORACLE\ADMIN\ORCL\UDUMP\orcl_ora_3452.trc

 

头块中的部分信息：

 

...

  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 82     #blocks: 11264
                  last map  0x00000000  #maps: 0      offset: 2716 
      Highwater::  0x01012f89  ext#: 81     blk#: 1024   ext size: 1024 
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 11137
  mapblk  0x00000000  offset: 81   
                   Unlocked
  --------------------------------------------------------

...

 

我们发现高水位并没有发生变化，目前高水位数据块仍为：

 

SQL> select to_number('12f89', 'xxxxxxx') from dual;

 

TO_NUMBER('12F89','XXXXXXX')
----------------------------
                       77705

 

由此可知，当对数据进行 delete 操作时，并不会缩减段的高水位，segment 的占用空间也没有变化。这样会导致查询时扫描不必要的块，还有可能会浪费存储空间。在 Oracle9i 中，你可以使用 move，从 Oracle10g 开始，你可以使用 shrink。

 

3. 查询表 test_yct 收缩后的状态

 

收缩表 test_yct 的存储空间：

 

-- 必须启用 row movement

SQL> alter table test_yct shrink space compact;
alter table test_yct shrink space compact
*
ERROR 位于第 1 行:
ORA-10636: ROW MOVEMENT is not enabled

 

SQL> alter table test_yct enable row movement;

 

表已更改。

 

-- 将数据挪动到表的前端,但不回收高水位.

SQL> alter table test_yct shrink space compact;

 

表已更改。

 

-- 收缩表，回收高水位

SQL> alter table test_yct shrink space;

 

表已更改。

 

查看收缩后 test_yct 的空间占用情况：

 

SQL> select count(*) from test_yct;

 

  COUNT(*)
----------
     97921

 

SQL> select bytes/1024/1024 from user_segments where segment_name='TEST_YCT';

 

BYTES/1024/1024
---------------
         9.6875

 

SQL> select EXTENT_ID,BYTES,BLOCKS from user_extents where segment_name='TEST_YCT';

 

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
        24     720896         88

 

已选择25行。