IDC资讯中心
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.
