ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 加强版hao_show_space,For ASSM and MSSM both

加强版hao_show_space,For ASSM and MSSM both

原创 Linux操作系统 作者:viadeazhu 时间:2009-07-17 20:12:11 0 删除 编辑

经典的为MSSM提供服务的show_space家喻户晓。我是个lazy guy,所以写了个hao_show_space同时自动适应ASSM和MSSM的segments。

主要是提供得比较人性化,其中把HWM的概念以更形象的方式显示出来。share给大家。

结果显示如下:

SQL> exec hao_show_space('hao');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "LONGRAW_USER.HAO"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................3
Last Used Ext BlockId...................14177
Last Used Block.........................3
PL/SQL procedure successfully completed.
 
SQL>  exec hao_show_space('uidx','i');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
MSSM tablespace:
The segment space usage for INDEX "LONGRAW_USER.UIDX"
##################################################
UNUSED BLOCKS...........................6
UNUSED Bytes............................49152
------------------------HWM------------------------
Free Blocks.............................0
Used Blocks.............................2
##################################################
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................5
Last Used Ext BlockId...................1345
Last Used Block.........................2
PL/SQL procedure successfully completed.
 
源码如下:
create or replace procedure hao_show_space (
segment_name varchar2,
segment_type varchar2 default 'TABLE',
segment_owner varchar default user
)
AUTHID CURRENT_USER
is
style. varchar2(10);
sqltext varchar2(1000);
UNFORMATTED_BLOCKS NUMBER;
UNFORMATTED_BYTES NUMBER;
FS1_BLOCKS NUMBER;
FS1_BYTES NUMBER;
 FS2_BLOCKS NUMBER;
 FS2_BYTES NUMBER;
 FS3_BLOCKS NUMBER;
 FS3_BYTES NUMBER;
 FS4_BLOCKS NUMBER;
 FS4_BYTES NUMBER;
 FULL_BLOCKS NUMBER;
 FULL_BYTES NUMBER;
v_segname varchar2(100);
v_type varchar2(10);
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
 dbms_output.put_line( rpad('#',50,'#'));
 dbms_output.put_line('--------hao_show_space kit created by Hao---------');
 dbms_output.put_line( rpad('#',50,'#'));
 v_segname := upper(segment_name);
 v_type := upper(segment_type);
 if (segment_type = 'i' or segment_type = 'I') then
 v_type := 'INDEX';
 end if;
 if (segment_type = 't' or segment_type = 'T') then
 v_type := 'TABLE';
 end if;
 if (segment_type = 'c' or segment_type = 'C') then
 v_type := 'CLUSTER';
 end if;
 sqltext:='select t.SEGMENT_SPACE_MANAGEMENT '||
 'from dba_segments s,dba_tablespaces t '||
 'where s.TABLESPACE_NAME=t.TABLESPACE_NAME '||
 'and  s.SEGMENT_TYPE='''||v_type||''' '||
 'and s.SEGMENT_NAME='''||v_segname||''' '||
 'and s.OWNER='''||segment_owner||'''';
-- dbms_output.put_line(sqltext);
 EXECUTE IMMEDIATE sqltext into style;
-- dbms_output.put_line('SEGMENT_SPACE_MANAGEMENT: '||style);
-- If the segment is in ASSM tablespace
if style='AUTO' then
 dbms_output.put_line('ASSM tablespace:');
 dbms_space.space_usage(
 segment_owner,
 v_segname,
 v_type,
 UNFORMATTED_BLOCKS,
 UNFORMATTED_BYTES,
 FS1_BLOCKS,
 FS1_BYTES,
 FS2_BLOCKS,
 FS2_BYTES,
 FS3_BLOCKS,
 FS3_BYTES,
 FS4_BLOCKS,
 FS4_BYTES,
 FULL_BLOCKS,
 FULL_BYTES);
--
dbms_space.unused_space
( segment_owner => segment_owner,
segment_name => v_segname,
segment_type => v_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 );
 --
 dbms_output.put_line('The segment space usage for '||v_type||' "'||segment_owner||'.'||v_segname||'"');
 dbms_output.put_line( rpad('#',50,'#'));
 p( 'UNUSED BLOCKS', l_unused_blocks );
 p( 'UNUSED Bytes', l_unused_bytes );
 dbms_output.put_line( rpad('-',24,'-')||'HWM'||rpad('-',24,'-'));
 p( 'UNFORMATTED_BLOCKS', UNFORMATTED_BLOCKS );
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FS1 Blocks', FS1_BLOCKS );
 p( 'FS1 Bytes ', FS1_BYTES );
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FS2 Blocks', FS2_BLOCKS );
 p( 'FS2 Bytes ', FS2_BYTES );
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FS3 Blocks', FS3_BLOCKS );
 p( 'FS3 Bytes ', FS3_BYTES );
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FS4 Blocks', FS4_BLOCKS );
 p( 'FS4 Bytes ', FS4_BYTES );
 dbms_output.put_line( rpad('-',50,'-'));
 p( 'FULL BLOCKS', FULL_BLOCKS );
 p( 'FULL_BYTES ', FULL_BYTES );
 dbms_output.put_line( rpad('#',50,'#'));
 p( 'Data Blocks(under HWM)', UNFORMATTED_BLOCKS+FS1_BLOCKS+FS2_BLOCKS+FS3_BLOCKS+FS4_BLOCKS+FULL_BLOCKS );
 p( 'All Blocks (under HWM)', l_total_blocks-l_unused_blocks );
 p( 'Total Blocks', l_total_blocks );
 p( 'Total Bytes',  l_total_bytes );
dbms_output.put_line( rpad('#',50,'#')); 
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
-- if the Manual Segment Space Management
ELSE
dbms_output.put_line('MSSM tablespace:');
dbms_output.put_line('The segment space usage for '||v_type||' "'||segment_owner||'.'||v_segname||'"');
dbms_output.put_line( rpad('#',50,'#'));
dbms_space.free_blocks
( segment_owner => segment_owner,
segment_name => v_segname,
segment_type => v_type,
freelist_group_id => 0,
free_blks => l_free_blks );
--
dbms_space.unused_space
( segment_owner => segment_owner,
segment_name => v_segname,
segment_type => v_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 );
--
p( 'UNUSED BLOCKS', l_unused_blocks );
p( 'UNUSED Bytes', l_unused_bytes );
dbms_output.put_line( rpad('-',24,'-')||'HWM'||rpad('-',24,'-'));
p( 'Free Blocks', l_free_blks );
p( 'Used Blocks', l_total_blocks-l_free_blks-l_unused_blocks );
dbms_output.put_line( rpad('#',50,'#'));
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
dbms_output.put_line( rpad('#',50,'#'));
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
END IF;
 END;
 /

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

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

注册时间:2008-08-22

  • 博文量
    79
  • 访问量
    368890