oracle数据块状态视图v$bh的用法

1.创建一个测试表，test，并且插入10000行数据；

begin
  for i in 1 .. 10000 loop
    execute immediate 'insert into test values(:x)'
      using i;
  end loop;
end;

2.创建一个存储过程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) 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