ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于SHOW_SPACE()工具的用法(转)

关于SHOW_SPACE()工具的用法(转)

原创 Linux操作系统 作者:47328983 时间:2012-05-24 09:01:51 0 删除 编辑
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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/241379/viewspace-730776/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    329296