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('Unused Bytes', l_unformatted_bytes);
p('Total Blocks', l_full_blocks);
p('Total bytes', l_full_bytes);
END IF;
END; 复制代码
使用方法：
_sys@FAKE> exec dexter.show_space('BASICC3','T','AUTO','Y') ;
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................18
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
from user_tables
where table_name in ('BASICC', 'BASICC2'); 复制代码注意，在创建compress 压缩表的时候指定pct_free的值，不然结果不严谨。另外做测试的时候，大家可以放心使用
create table basicc2 tablespace users compress as select * from dba_objects ; 复制代码 create table as select 的方式创建压缩表，因为它本身使用的是direct pathwrite/read。