Oracle10g新特性shrink segment来降低HWM

overmars · 发表于 2004-3-19 15:44

通过oracle10g的文档我们知道可以通过使用alter table <table_name> shrink space来减低表的HWM，以前必须要使用truncate来降低表的HWM，下面是操作的过程：

SQL> create table demo as select * from dba_source;

Table created.

Elapsed: 00:00:05.83
SQL> select count(*) from demo;

  COUNT(*)
----------
210992

Elapsed: 00:00:01.06
SQL> insert into demo select * from demo;

210992 rows created.

Elapsed: 00:00:59.83
SQL> commit;

Commit complete.

//得到一个40万条记录的表，下面来查看这个表空间分布情况。

SQL> exec show_space('demo','auto');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SQL> set serveroutput on
SQL>  exec show_space('demo','auto');
Total Blocks............................9216
Total Bytes.............................75497472
Unused Blocks...........................768
Unused Bytes............................6291456
Last Used Ext FileId....................4
Last Used Ext BlockId...................8328
Last Used Block.........................256

一共有9216个数据块，HWM在9216-768=8448这个块.
也可以通过查看extents得到HWM=8*16+128*63+256=8192+256=8448

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> delete from demo where rownum<220000;

219999 rows deleted.

Elapsed: 00:00:40.99
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>  exec show_space('demo','auto');
Total Blocks............................9216
Total Bytes.............................75497472
Unused Blocks...........................768
Unused Bytes............................6291456
Last Used Ext FileId....................4
Last Used Ext BlockId...................8328
Last Used Block.........................256

PL/SQL procedure successfully completed.

//删除操作后表的HWM没有变化，还是在第8448块这个位置。
Elapsed: 00:00:00.00
SQL> alter table demo shrink space;
alter table demo shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

//先要enable row movement才能shrink
Elapsed: 00:00:00.09
SQL> alter table demo enable row movement;

Table altered.

Elapsed: 00:00:00.10
SQL>  alter table demo shrink space;

Table altered.

Elapsed: 00:01:35.51
SQL>  exec show_space('demo','auto');
Total Blocks............................3656
Total Bytes.............................29949952
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................3720
Last Used Block.........................72

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

//可以看到HWM降到了3656这个块上面!