ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 自定义show_space过程来显示数据段的利用信息

自定义show_space过程来显示数据段的利用信息

原创 Linux操作系统 作者:eric0435 时间:2013-08-27 08:35:21 0 删除 编辑
SHOW_SPACE过程
SHOW_SPACE例程用于打印数据库段空间利用率信息:
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 )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE, and so it can be installed
-- once per database, instead of once per user who wanted to use it
  authid current_user
as
  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;
  l_segment_space_mgmt varchar2(255);
  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;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
  dbms_output.put_line( rpad(p_label,40,'.') ||
  to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is an ASSM object or not
  begin
    execute immediate
    'select ts.segment_space_management
    from dba_segments seg, dba_tablespaces ts
    where seg.segment_name = :p_segname
    and (:p_partition is null or
    seg.partition_name = :p_partition)
    and seg.owner = :p_owner
    and seg.tablespace_name = ts.tablespace_name'
    into l_segment_space_mgmt
    using p_segname, p_partition, p_partition, p_owner;
  exception
  when too_many_rows then
    dbms_output.put_line
      ( 'This must be a partitioned table, use p_partition => ');
    return;
  end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, otherwise we use the FREE_BLOCKS
-- API for the user-managed segments
  if l_segment_space_mgmt = 'AUTO'   then
     dbms_space.space_usage
     ( p_owner, p_segname, p_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_partition);
     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 => 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;
-- and then the unused space API call to get the rest of the
-- information
  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( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
     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;
/

创建如下:
sys@DEVELOP> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner in varchar2 default user,
  4    p_type in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE, and so it can be installed
  8  -- once per database, instead of once per user who wanted to use it
  9    authid current_user
 10  as
 11    l_free_blks number;
 12    l_total_blocks number;
 13    l_total_bytes number;
 14    l_unused_blocks number;
 15    l_unused_bytes number;
 16    l_LastUsedExtFileId number;
 17    l_LastUsedExtBlockId number;
 18    l_LAST_USED_BLOCK number;
 19    l_segment_space_mgmt varchar2(255);
 20    l_unformatted_blocks number;
 21    l_unformatted_bytes number;
 22    l_fs1_blocks number; l_fs1_bytes number;
 23    l_fs2_blocks number; l_fs2_bytes number;
 24    l_fs3_blocks number; l_fs3_bytes number;
 25    l_fs4_blocks number; l_fs4_bytes number;
 26    l_full_blocks number; l_full_bytes number;
 27  -- inline procedure to print out numbers nicely formatted
 28  -- with a simple label
 29  procedure p( p_label in varchar2, p_num in number )
 30  is
 31  begin
 32    dbms_output.put_line( rpad(p_label,40,'.') ||
 33    to_char(p_num,'999,999,999,999') );
 34  end;
 35  begin
 36  -- this query is executed dynamically in order to allow this procedure
 37  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 38  -- via a role as is customary.
 39  -- NOTE: at runtime, the invoker MUST have access to these two
-- views!
 40   41  -- this query determines if the object is an ASSM object or not
 42    begin
 43      execute immediate
 44      'select ts.segment_space_management
 45      from dba_segments seg, dba_tablespaces ts
 46      where seg.segment_name = :p_segname
    and (:p_partition is null or
 47   48      seg.partition_name = :p_partition)
 49      and seg.owner = :p_owner
 50      and seg.tablespace_name = ts.tablespace_name'
 51      into l_segment_space_mgmt
 52      using p_segname, p_partition, p_partition, p_owner;
 53    exception
 54    when too_many_rows then
 55      dbms_output.put_line
 56        ( 'This must be a partitioned table, use p_partition => ');
 57      return;
 58    end;
 59  -- if the object is in an ASSM tablespace, we must use this API
 60  -- call to get space information, otherwise we use the FREE_BLOCKS
 61  -- API for the user-managed segments
 62    if l_segment_space_mgmt = 'AUTO'   then
 63       dbms_space.space_usage
 64       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 65       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 66       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 67       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 68       p( 'Unformatted Blocks ', l_unformatted_blocks );
 69       p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
 70       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 71       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 72       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 73       p( 'Full Blocks ', l_full_blocks );
 74    else
 75       dbms_space.free_blocks(
 76       segment_owner => p_owner,
 77       segment_name => p_segname,
 78       segment_type => p_type,
 79       freelist_group_id => 0,
 80       free_blks => l_free_blks);
 81       p( 'Free Blocks', l_free_blks );
 82    end if;
 83  -- and then the unused space API call to get the rest of the
 84  -- information
 85    dbms_space.unused_space
 86     ( segment_owner => p_owner,
 87       segment_name => p_segname,
 88       segment_type => p_type,
 89       partition_name => p_partition,
 90       total_blocks => l_total_blocks,
 91       total_bytes => l_total_bytes,
 92       unused_blocks => l_unused_blocks,
 93       unused_bytes => l_unused_bytes,
 94       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 95       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 96       LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 97       p( 'Total Blocks', l_total_blocks );
 98       p( 'Total Bytes', l_total_bytes );
 99       p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100       p( 'Unused Blocks', l_unused_blocks );
101       p( 'Unused Bytes', l_unused_bytes );
102       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104       p( 'Last Used Block', l_LAST_USED_BLOCK );
105  end;
106  /

Procedure created.

show_space过程包含以下参数:
sys@DEVELOP> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT


参数如下:
 P_SEGNAME:段名(例如,表或索引名)。
 P_OWNER:默认为当前用户,不过也可以使用这个例程查看另外某个用户。
 P_TYPE:默认为TABLE,这个参数表示查看哪种类型的对象(段)。例如,SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS 会列出合法的段类型。
?P_PARTITION:显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。


这个过程的输出如下,这里段位于一个自动段空间管理(Automatic Segment Space Management, ASSM)表空间中:
sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              12
Full Blocks ............................          16,305
Total Blocks............................         146,304
Total Bytes.............................   1,198,522,368
Total MBytes............................           1,143
Unused Blocks...........................         129,920
Unused Bytes............................   1,064,304,640
Last Used Ext FileId....................              10
Last Used Ext BlockId...................         348,800
Last Used Block.........................           8,192

PL/SQL procedure successfully completed.

报告的各项结果说明如下:
 Unformatted Blocks:为表分配的位于高水位线(high-water mark, HWM)之下但未用的块数。把未格式化和未用的块加在一 起,就是已为表分配但从未用于保存ASSM对象数据的总块数。
 FS1 Blocks-FS4 Blocks:包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如,(0-25)是指空闲度为 0~25%的块数。
Full Blocks:已满的块数,不能再对这些执行插入。
 Total Blocks、Total bytes、Total Mbytes:为所查看的段分配的总空间量,单位分别是数据库块、字节和兆字节。
 Unused Blocks、Unused Bytes:表示未用空间所占的比例(未用空间量)。这些块已经分配给所查看的段,但目前在段的HWM 之上。
 Last Used Ext FileId:最后使用的文件的文件ID,该文件包含最后一个含数据的区段(extent)。
 Last Used Ext BlockId:最后一个区段开始处的块ID;这是最后使用的文件中的块ID。
 Last Used Block:最后一个区段中最后一个块的偏移量。

如果对象在用户空间管理的表空间中,使用SHOW_SPACE查看时,输出如下:
sys@DEVELOP> exec show_space('T1');
Free Blocks.............................               3
Total Blocks............................          17,408
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             869
Unused Bytes............................       7,118,848
Last Used Ext FileId....................               1
Last Used Ext BlockId...................       1,696,896
Last Used Block.........................             155

PL/SQL procedure successfully completed.

这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist(自由列表)组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组,还需要修改这个脚本。

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

请登录后发表评论 登录
全部评论
Oracle ACE 系统架构师

注册时间:2011-10-12

  • 博文量
    560
  • 访问量
    6231107