存放历史数据库的服务器又面临空间不足的问题了，这次我们希望通过压缩表来获得更多的空间，可以存放更多的数据总是更好的。

之前压缩表我们用的很少，所以直到前不久参加合作伙伴的11gr2的推广活动时，我还以为这是11g的新特性，r2时进行了改进。至少oracle是宣称空间节省很多，性能损失很小的。

既然不是11g的新特性，上午对压缩的效果进行了一些测试，不是非常的满意，可能我预期的压缩比太高了。

首先是针对一层分区的表的测试：

set serveroutput on
set timing on

SQL> exec show_space('CHARGED_CARDS','PCARD','TABLE PARTITION','PCHARGED_DATA_20060801');
Total Blocks............................256000
Total Bytes.............................2097152000
Unused Blocks...........................10752
Unused Bytes............................88080384
Last Used Ext FileId....................10
Last Used Ext BlockId...................243209
Last Used Block.........................2048

PL/SQL procedure successfully completed.

SQL> alter table pcard.charged_cards move partition PCHARGED_DATA_20060801 compress;

Table altered.

Elapsed: 00:03:05.45
SQL> exec show_space('CHARGED_CARDS','PCARD','TABLE PARTITION','PCHARGED_DATA_20060801');
Total Blocks............................217600
Total Bytes.............................1782579200
Unused Blocks...........................980
Unused Bytes............................8028160
Last Used Ext FileId....................10
Last Used Ext BlockId...................460809
Last Used Block.........................11820

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

原来2.1G的空间压缩后空间是1.8G，节省300M，但是由于仍然使用原有的表空间，压缩过程中原来的数据文件扩充到3.8G，且压缩结束后，数据文件所占的空间不能回收。

压缩并移动到其他表空间，可以便于空间的回收：
SQL> alter table pcard.charged_cards move partition PCHARGED_DATA_20060801 tablespace PCHARGED_DATA_P04 compress;

Table altered.

Elapsed: 00:02:23.90
SQL> exec show_space('CHARGED_CARDS','PCARD','TABLE PARTITION','PCHARGED_DATA_20060801');
Total Blocks............................217600
Total Bytes.............................1782579200
Unused Blocks...........................980
Unused Bytes............................8028160
Last Used Ext FileId....................31
Last Used Ext BlockId...................204809
Last Used Block.........................11820

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

下面打算测试复合分区的情况，外面是范围分区，里面是列表分区：

从测试的效果上看，列表分区还不支持move（测试数据库版本10.2.0.1）：

SQL> alter table pcard.pay_detail move partition PDETAIL_DATA_200708 tablespace PDETAIL_DATA_TB0 compress;
alter table pcard.pay_detail move partition PDETAIL_DATA_200708 tablespace PDETAIL_DATA_TB0 compress
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition

但是到了11g以后，应该可以对列表分区进行类似的操作了：
ORA-14257: cannot move partition other than a Range, List, System, or Hash partition
Cause: User attempt to move a partition that is not a Range,List,System, or Hash partition which is illegal
Action: Specify MOVE PARTITION for a Range,List,System, or Hash partition only

SQL> alter table pcard.pay_detail move subpartition PDETAIL_DATA_200708_SP1 tablespace PDETAIL_DATA_TB0 compress;
alter table pcard.pay_detail move subpartition PDETAIL_DATA_200708_SP1 tablespace PDETAIL_DATA_TB0 compress
*
ERROR at line 1:
ORA-14160: this physical attribute may not be specified for a table
subpartition

到11g以后，子分区的move仍然不能使用compress子句，还是要寄希望于外面那层分区可以直接move并压缩：
ORA-14160: this physical attribute may not be specified for a table subpartition
Cause: unexpected option was encountered while parsing physical attributes of a table subpartition; TABLESPACE is the only valid option
Action: remove invalid option(s)

SQL> alter table pcard.pay_detail move partition PDETAIL_DATA_200708 compress;
alter table pcard.pay_detail move partition PDETAIL_DATA_200708 compress
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition

所以，还是要期待11g的测试结果啦。