Oracle压缩功能小结1--压缩功能介绍 - 数据库编程

partition_name => p_partition, unformatted_blocks=> l_unformatted_blocks, unformatted_bytes =>l_unformatted_bytes, fs1_blocks => l_fs1_blocks, fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks, fs2_bytes => l_fs2_bytes, fs3_blocks => l_fs3_blocks, fs3_bytes => l_fs3_bytes, fs4_blocks => l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks => l_full_blocks, full_bytes => l_full_bytes); DBMS_OUTPUT.put_line(RPAD('', 50, '*')); DBMS_OUTPUT.put_line('Thesegment is analyzed'); p('0%-- 25% free spaceblocks', l_fs1_blocks); p('0%-- 25% free spacebytes', l_fs1_bytes); p('25% -- 50% free spaceblocks', l_fs2_blocks); p('25% -- 50% free spacebytes', l_fs2_bytes); p('50% -- 75% free spaceblocks',l_fs3_blocks); p('50% -- 75% free spacebytes', l_fs3_bytes); p('75% -- 100% free spaceblocks', l_fs4_blocks); p('75% -- 100% free spacebytes', l_fs4_bytes); p('Unused Blocks', l_unformatted_blocks); p('UnusedBytes', l_unformatted_bytes); p('Total Blocks', l_full_blocks); p('Total bytes', l_full_bytes); END IF; END;

使用方法：

_sys@FAKE> execdexter.show_space('BASICC3','T','AUTO','Y') ;
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................18
Unused Bytes............................147456
Last Used Ext FileId....................4
Last Used Ext BlockId...................584704
Last Used Block.........................110
Thesegment is analyzed
0% -- 25% free spaceblocks..............0
0% -- 25% free spacebytes...............0
25% -- 50% free spaceblocks.............0
25% -- 50% free spacebytes..............0
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............43
75% -- 100% free spacebytes.............352256
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................309
Total bytes.............................2531328
 
PL/SQL procedure successfully completed.
 
_sys@FAKE> exec dexter.show_space('BASICC2');
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................54
Unused Bytes............................442368
Last Used Ext FileId....................4
Last Used Ext BlockId...................584320
Last Used Block.........................74

小脚本：

select table_name, compress_for, compression,PCT_FREE, PCT_USED
  fromuser_tables
 wheretable_name in ('BASICC', 'BASICC2');

注意，在创建compress压缩表的时候指定pct_free的值，不然结果不严谨。另外做测试的时候，大家可以放心使用

create table basicc2 tablespace users compressas select * from dba_objects ;

create table as select 的方式创建压缩表，因为它本身使用的是direct pathwrite/read。

附件2 ACO 白皮书

aco白皮书下载地址：

http://download.csdn.net/detail/renfengjun/7514403

Oracle压缩功能小结1--压缩功能介绍(三)

附件2 ACO 白皮书