关于SHOW_SPACE()工具的用法

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 suclearcase/" target="_blank" >ccessfully 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.

原文转自：http://www.ltesting.net