show_space输出

    关于show_space的代码网上很多，大家也改了很多，本门末尾贴上itpub过来的“超级完全无敌”版

    但是关于数据结果缺不是很多总结，这里记录一下：

如：

EXEC Show_Space('WMS_BA_MQ_RECEIVE_ERP','auto','T','Y');
Total Blocks............................372992
Total Bytes.............................3055550464
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................14
Last Used Ext BlockId...................949257
Last Used Block.........................128
 *************************************************
The segment is analyzed
0% -- 25% free space blocks.............2
0% -- 25% free space bytes..............16384
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........267556
75% -- 100% free space bytes............2191818752
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................104582
Total bytes.............................856735744
PL/SQL procedure successfully completed.
Elapsed: 00:00:03:48

Last Used Ext FileId:最后使用的文件的文件ID，该文件包含最后一个含数据的区段（extent)
Last Used Ext BlockId:最后一个区段开始处的块ID；这是最后使用的文件中的块ID
Last Used Block:最后一个区段中最后一个块的偏移量

x% free space blocks:空闲度为x%的块数
Unused Blocks:未用的块数——就是shrink能够释放的空间

    运行方法：在segment owner下create procedure，然后

EXEC Show_Space('WMS_BA_MQ_RECEIVE_ERP','auto','T','Y');

源码：

CREATE OR REPLACE PROCEDURE Show_Space
( p_segname_1 IN VARCHAR2,
p_space IN VARCHAR2 DEFAULT 'MANUAL',
p_type_1 IN VARCHAR2 DEFAULT 'TABLE' ,
p_analyzed IN VARCHAR2 DEFAULT 'N',
p_owner_1 IN VARCHAR2 DEFAULT USER)
AS
p_segname VARCHAR2(100);
p_type VARCHAR2(10);
p_owner VARCHAR2(30);

l_unformatted_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_fs1_blocks NUMBER;
l_fs1_bytes NUMBER;
l_fs2_blocks NUMBER;
l_fs2_bytes NUMBER;
l_fs3_blocks NUMBER;
l_fs3_bytes NUMBER;
l_fs4_blocks NUMBER;
l_fs4_bytes NUMBER;
l_full_blocks NUMBER;
l_full_bytes NUMBER;

l_free_blks NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_LastUsedExtFileId NUMBER;
l_LastUsedExtBlockId NUMBER;
l_LAST_USED_BLOCK NUMBER;

PROCEDURE p( p_label IN VARCHAR2, p_num IN NUMBER )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD(p_label,40,'.') ||
p_num );
END;
BEGIN
p_segname := UPPER(p_segname_1); -- rainy changed
p_owner := UPPER(p_owner_1);
p_type := p_type_1;

IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN --rainy changed
p_type := 'INDEX';
END IF;

IF (p_type_1 = 't' OR p_type_1 = 'T') THEN --rainy changed
p_type := 'TABLE';
END IF;

IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN --rainy changed
p_type := 'CLUSTER';
END IF;


dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );

p( 'Free Blocks', l_free_blks );
END IF;

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );


/*IF the segment is analyzed */
IF p_analyzed = 'Y' THEN
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
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('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', 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;
/