關於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 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.