oracle 9i hwm,Oracle10g新特性shrink segment来降低HWM

通过oracle10g的文档我们知道可以通过使用alter table 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这个块上面!