show_space

create or replace procedure show_space(p_segname_1 in varchar2,
                                       p_owner_1   in varchar2 default user,
                                       p_type_1    in varchar2 default 'TABLE',
                                       p_space     in varchar2 default 'AUTO',
                                       p_analyzed  in varchar2 default 'Y') 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;
/

用法 SQL > create table t  as  select * from dba_objects;

Table created.

SQL > set serverout on
SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
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 .. .. .. .. .. .. .. .. .. .. .. .. .. .. 696
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .5701632

PL
/
SQL procedure successfully completed.

SQL > delete from t;

50602 rows deleted.

SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 768
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .6291456
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .52
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 425984
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .1033
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .76
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
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...........696
75% -- 100% free space bytes............5701632
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .0
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0

PL
/
SQL procedure successfully completed.

SQL > alter table t move;

Table altered.

SQL > exec show_space('T', 'TEST');
Total Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .. 8
Total Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .65536
Unused Blocks .. .. .. .. .. .. .. .. .. .. .. .. .. .5
Unused Bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. 40960
Last Used Ext FileId .. .. .. .. .. .. .. .. .. .. 4
Last Used Ext BlockId .. .. .. .. .. .. .. .. .. .401
Last Used Block .. .. .. .. .. .. .. .. .. .. .. .. .3
** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
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 .. .. .. .. .. .. .. .. .. .. .. .. .. .. 0
Total bytes .. .. .. .. .. .. .. .. .. .. .. .. .. .. .0

PL
/
SQL procedure successfully completed