经典的为MSSM提供服务的show_space家喻户晓。我是个lazy guy，所以写了个hao_show_space同时自动适应ASSM和MSSM的segments。

主要是提供得比较人性化，其中把HWM的概念以更形象的方式显示出来。share给大家。

结果显示如下：

SQL> exec hao_show_space('hao');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "LONGRAW_USER.HAO"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................3
Last Used Ext BlockId...................14177
Last Used Block.........................3

PL/SQL procedure successfully completed.

SQL>  exec hao_show_space('uidx','i');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
MSSM tablespace:
The segment space usage for INDEX "LONGRAW_USER.UIDX"
##################################################
UNUSED BLOCKS...........................6
UNUSED Bytes............................49152
------------------------HWM------------------------
Free Blocks.............................0
Used Blocks.............................2
##################################################
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................5
Last Used Ext BlockId...................1345
Last Used Block.........................2

PL/SQL procedure successfully completed.

源码如下：

create or replace procedure hao_show_space (
segment_name varchar2,
segment_type varchar2 default 'TABLE',
segment_owner varchar default user
)
AUTHID CURRENT_USER
is
style. varchar2(10);
sqltext varchar2(1000);
UNFORMATTED_BLOCKS NUMBER;
UNFORMATTED_BYTES NUMBER;
FS1_BLOCKS NUMBER;
FS1_BYTES NUMBER;
 FS2_BLOCKS NUMBER;
 FS2_BYTES NUMBER;
 FS3_BLOCKS NUMBER;
 FS3_BYTES NUMBER;
 FS4_BLOCKS NUMBER;
 FS4_BYTES NUMBER;
 FULL_BLOCKS NUMBER;
 FULL_BYTES NUMBER;
v_segname varchar2(100);
v_type varchar2(10);
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
 dbms_output.put_line( rpad('#',50,'#')); 
 dbms_output.put_line('--------hao_show_space kit created by Hao---------');
 dbms_output.put_line( rpad('#',50,'#')); 
 v_segname := upper(segment_name); 
 v_type := upper(segment_type); 
 if (segment_type = 'i' or segment_type = 'I') then 
 v_type := 'INDEX'; 
 end if; 
 if (segment_type = 't' or segment_type = 'T') then 
 v_type := 'TABLE'; 
 end if; 
 if (segment_type = 'c' or segment_type = 'C') then 
 v_type := 'CLUSTER'; 
 end if; 
 sqltext:='select t.SEGMENT_SPACE_MANAGEMENT '||
 'from dba_segments s,dba_tablespaces t '||
 'where s.TABLESPACE_NAME=t.TABLESPACE_NAME '||
 'and  s.SEGMENT_TYPE='''||v_type||''' '||
 'and s.SEGMENT_NAME='''||v_segname||''' '||
 'and s.OWNER='''||segment_owner||'''';
-- dbms_output.put_line(sqltext);
 EXECUTE IMMEDIATE sqltext into style;
-- dbms_output.put_line('SEGMENT_SPACE_MANAGEMENT: '||style);
-- If the segment is in ASSM tablespace
if style='AUTO' then
 dbms_output.put_line('ASSM tablespace:');
 dbms_space.space_usage(
 segment_owner,
 v_segname,
 v_type,
 UNFORMATTED_BLOCKS,
 UNFORMATTED_BYTES,
 FS1_BLOCKS,
 FS1_BYTES,
 FS2_BLOCKS,
 FS2_BYTES,
 FS3_BLOCKS,
 FS3_BYTES,
 FS4_BLOCKS,
 FS4_BYTES,
 FULL_BLOCKS,
 FULL_BYTES);
--
dbms_space.unused_space 
( segment_owner => segment_owner, 
segment_name => v_segname, 
segment_type => v_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 ); 
 --
 dbms_output.put_line('The segment space usage for '||v_type||' "'||segment_owner||'.'||v_segname||'"');
 dbms_output.put_line( rpad('#',50,'#')); 
 p( 'UNUSED BLOCKS', l_unused_blocks ); 
 p( 'UNUSED Bytes', l_unused_bytes ); 
 dbms_output.put_line( rpad('-',24,'-')||'HWM'||rpad('-',24,'-'));
 p( 'UNFORMATTED_BLOCKS', UNFORMATTED_BLOCKS ); 
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FS1 Blocks', FS1_BLOCKS ); 
 p( 'FS1 Bytes ', FS1_BYTES ); 
 dbms_output.put_line( rpad('-',50,'-')); 
 p( 'FS2 Blocks', FS2_BLOCKS ); 
 p( 'FS2 Bytes ', FS2_BYTES ); 
 dbms_output.put_line( rpad('-',50,'-')); 
 p( 'FS3 Blocks', FS3_BLOCKS ); 
 p( 'FS3 Bytes ', FS3_BYTES ); 
 dbms_output.put_line( rpad('-',50,'-')); 
 p( 'FS4 Blocks', FS4_BLOCKS ); 
 p( 'FS4 Bytes ', FS4_BYTES ); 
 dbms_output.put_line( rpad('-',50,'-')); 
 p( 'FULL BLOCKS', FULL_BLOCKS ); 
 p( 'FULL_BYTES ', FULL_BYTES ); 
 dbms_output.put_line( rpad('#',50,'#')); 
 p( 'Data Blocks(under HWM)', UNFORMATTED_BLOCKS+FS1_BLOCKS+FS2_BLOCKS+FS3_BLOCKS+FS4_BLOCKS+FULL_BLOCKS );
 p( 'All Blocks (under HWM)', l_total_blocks-l_unused_blocks );
 p( 'Total Blocks', l_total_blocks ); 
 p( 'Total Bytes',  l_total_bytes ); 
dbms_output.put_line( rpad('#',50,'#'));  
p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 
p( 'Last Used Block', l_LAST_USED_BLOCK ); 
-- if the Manual Segment Space Management
ELSE 
dbms_output.put_line('MSSM tablespace:');
dbms_output.put_line('The segment space usage for '||v_type||' "'||segment_owner||'.'||v_segname||'"');
dbms_output.put_line( rpad('#',50,'#')); 
dbms_space.free_blocks 
( segment_owner => segment_owner, 
segment_name => v_segname, 
segment_type => v_type, 
freelist_group_id => 0, 
free_blks => l_free_blks );
--
dbms_space.unused_space 
( segment_owner => segment_owner, 
segment_name => v_segname, 
segment_type => v_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 ); 
--
p( 'UNUSED BLOCKS', l_unused_blocks ); 
p( 'UNUSED Bytes', l_unused_bytes ); 
dbms_output.put_line( rpad('-',24,'-')||'HWM'||rpad('-',24,'-'));
p( 'Free Blocks', l_free_blks ); 
p( 'Used Blocks', l_total_blocks-l_free_blks-l_unused_blocks ); 
dbms_output.put_line( rpad('#',50,'#')); 
p( 'Total Blocks', l_total_blocks ); 
p( 'Total Bytes', l_total_bytes ); 
dbms_output.put_line( rpad('#',50,'#')); 
p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 
p( 'Last Used Block', l_LAST_USED_BLOCK ); 
END IF;
 END;
 /