SHOW_SPACE是TOM写的一个小工具，SHOW_SPACE实际上就是一个存储过程，这个存储过程可以用来分析空间按使用情况，十分的方便。
以下为SHOW_SPACE的脚本：
create or replace procedure show_space( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL )as 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_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, 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( '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 );end;
此工具的使用方法为：
SQL> create table t as select * from all_users;
表已创建。
SQL> set serveroutput on;SQL> exec show_space('T');Total Blocks............................8Total Bytes.............................65536Unused Blocks...........................6Unused Bytes............................49152Last Used Ext FileId....................1Last Used Ext BlockId...................60745Last Used Block.........................2
PL/SQL 过程已成功完成。
不过此版本只适合表空间为非ASSM的时候，当表空间为ASSM时不能用，因为DBMS_SPACE.FREE_BLOCKS不允许在ASSM上操作。要想在ASSM表空间的时候也能使用则可以使用以下版本：
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)asp_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 )isbegindbms_output.put_line( rpad(p_label,40,'.') ||p_num );end;beginp_segname := upper(p_segname_1);p_owner := upper(p_owner_1);p_type := p_type_1;if (p_type_1 = 'i' or p_type_1 = 'I') thenp_type := 'INDEX';end if;if (p_type_1 = 't' or p_type_1 = 'T') thenp_type := 'TABLE';end if;if (p_type_1 = 'c' or p_type_1 = 'C') thenp_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') thendbms_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' thendbms_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;
以下是此版本的SHOW_SPACE的使用方法简介：
ASSM 类型的表 SQL> exec show_space('t','auto'); Total Blocks............................512 Total Bytes.............................4194304 Unused Blocks...........................78 Unused Bytes............................638976 Last Used Ext FileId....................9 Last Used Ext BlockId...................25608 Last Used Block.........................50 PL/SQL procedure successfully completed. ASSM 类型的索引 SQL> exec show_space('t_index','auto','i'); Total Blocks............................80 Total Bytes.............................655360 Unused Blocks...........................5 Unused Bytes............................40960 Last Used Ext FileId....................9 Last Used Ext BlockId...................25312 Last Used Block.........................3 PL/SQL procedure successfully completed. 对analyze 过的segment 可以这样 SQL> exec show_space('t','auto','T','Y'); Total Blocks............................512 Total Bytes.............................4194304 Unused Blocks...........................78 Unused Bytes............................638976 Last Used Ext FileId....................9 Last Used Ext BlockId...................25608 Last Used Block.........................50 ************************************************* The segment is analyzed 0% -- 25% free space blocks.............0 0% -- 25% free space bytes..............0 25% -- 50% free space blocks............0 25% -- 50% free space bytes.............0 50% -- 75% free space blocks............0 50% -- 75% free space bytes.............0 75% -- 100% free space blocks...........0 75% -- 100% free space bytes............0 Unused Blocks...........................0 Unused Bytes............................0 Total Blocks............................418 Total bytes.............................3424256 PL/SQL procedure successfully completed.
