Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1102714
  • 博文数量: 276
  • 博客积分: 10077
  • 博客等级: 上将
  • 技术积分: 2513
  • 用 户 组: 普通用户
  • 注册时间: 2007-08-24 20:31
文章分类

全部博文(276)

文章存档

2020年(1)

2015年(5)

2012年(2)

2011年(6)

2010年(7)

2009年(224)

2008年(31)

我的朋友

分类: Oracle

2008-12-24 17:26:16

dbms_space.space_usage shows the space usage of data blocks under the segment High Water Mark.This procedure can only be used on tablespace that are created with auto segment space
management.
=========================
CREATE OR REPLACE PROCEDURE SHOW_SPACE_ASSM(P_SEGNAME IN VARCHAR2,
                                            P_OWNER   IN VARCHAR2 DEFAULT USER,
                                            P_TYPE    IN VARCHAR2 DEFAULT 'TABLE') AS
  l_fs1_bytes          number;
  l_fs2_bytes          number;
  l_fs3_bytes          number;
  l_fs4_bytes          number;
  l_fs1_blocks         number;
  l_fs2_blocks         number;
  l_fs3_blocks         number;
  l_fs4_blocks         number;
  l_full_bytes         number;
  l_full_blocks        number;
  l_unformatted_bytes  number;
  l_unformatted_blocks number;
begin
  dbms_space.space_usage(segment_owner      => P_OWNER,
                         segment_name       => P_SEGNAME,
                         segment_type       => P_TYPE,
                         fs1_bytes          => l_fs1_bytes,
                         fs1_blocks         => l_fs1_blocks,
                         fs2_bytes          => l_fs2_bytes,
                         fs2_blocks         => l_fs2_blocks,
                         fs3_bytes          => l_fs3_bytes,
                         fs3_blocks         => l_fs3_blocks,
                         fs4_bytes          => l_fs4_bytes,
                         fs4_blocks         => l_fs4_blocks,
                         full_bytes         => l_full_bytes,
                         full_blocks        => l_full_blocks,
                         unformatted_blocks => l_unformatted_blocks,
                         unformatted_bytes  => l_unformatted_bytes);
  dbms_output.put_line(' FS1 Blocks = ' || l_fs1_blocks || ' Bytes = ' ||
                       l_fs1_bytes);
  dbms_output.put_line(' FS2 Blocks = ' || l_fs2_blocks || ' Bytes = ' ||
                       l_fs1_bytes);
  dbms_output.put_line(' FS3 Blocks = ' || l_fs3_blocks || ' Bytes = ' ||
                       l_fs1_bytes);
  dbms_output.put_line(' FS4 Blocks = ' || l_fs4_blocks || ' Bytes = ' ||
                       l_fs1_bytes);
  dbms_output.put_line('Full Blocks = ' || l_full_blocks || ' Bytes =' ||
                       l_full_bytes);
  dbms_output.put_line('unformatted_blocks Blocks = ' ||
                       l_unformatted_blocks || ' Bytes =' ||
                       l_unformatted_bytes);
end;
======================================
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_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_space.free_blocks(segment_owner     => p_owner,
                         segment_name      => p_segname,
                         segment_type      => p_type,
                         partition_name    => p_partition,
                         freelist_group_id => 0,
                         free_blks         => l_free_blks);
  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('Free Blocks', l_free_blks);
  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;
=========================================
阅读(1028) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~