tom写了个好工具show_space,这个工具对于oracle来讲其实就是个存储过程，这个存储过程可以用来分析空间使用情况，有了此工具，就不用再通过写sql语句来看每条记录或表占用表空间的大小了，使用起来很方便。
具体使用过程如下：
首先需要创建一个存储过程:
d:/>sqlplus /nolog
sql>connect / as sysdba
sql>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;
/
procedure created.
执行以上语句会在当前用户下生成一个procedure，当前用户为sys用户。
sql>create table t as select * from all_users; (创建表t)
sql> exec show_space('t'); (查看表t占用空间大小)
free blocks.............................0
total blocks............................15
total bytes.............................61440
unused blocks...........................13
unused bytes............................53248
last used ext fileid....................13
last used ext blockid...................61782
last used block.........................2
结果马上就出来了，以前必须通过sql语句查询dba_tables才能得到结果，可见，此工具的方便性。
另外，此工具有好几个版本，目前上面这个版本只适合表空间为非assm的时候，assm的时候是不能用的，原因是dbms_space.free_blocks 不允许在assm上操作，解决方法如下：
对于assm，可以使用dbms_space.space_usage ，可以在show_space中加入这一段：
select ts.segment_space_management
into t_segment_space_management
from dba_segments seg
, dba_tablespaces ts
where seg.segment_name = t_segname
and seg.owner = t_owner
and seg.tablespace_name = ts.tablespace_name
;
--
if t_segment_space_management = 'auto'
then
dbms_space.space_usage (
t_owner,
t_segname,
t_type,
l_unformatted_blocks,
l_unformatted_bytes,
l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes,
l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes,
l_full_blocks, l_full_bytes
);
--
p( 'unformatted blocks ', l_unformatted_blocks );
p( 'fs1 blocks (0-25) ', l_fs1_blocks );
p( 'fs2 blocks (25-50) ', l_fs2_blocks );
p( 'fs3 blocks (50-75) ', l_fs3_blocks );
p( 'fs4 blocks (75-100)', l_fs4_blocks );
p( 'full blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => t_owner,
segment_name => t_segname,
segment_type => t_type,
freelist_group_id => 0,
free_blks => l_free_blks
);
--
p( 'free blocks', l_free_blks );
end if;
itpub上提供了该工具的很多版本，具体还有以下几个版本，也很好用，对今后的工具大有好处！
xzh2000 提供的最终混合超级完全无敌版：
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;
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.
