show_space procedure from asktom

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 )
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;

    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
  if p_partition is not null
  then
    select ts.segment_space_management
      into l_segment_space_mgmt
      from dba_segments seg,  dba_tablespaces  ts
     where seg.segment_name    = p_segname
       and seg.partition_name  = p_partition
       and   seg.owner           = p_owner
       and   seg.tablespace_name = ts.tablespace_name;
  else
    select ts.segment_space_management
      into l_segment_space_mgmt
      from dba_segments seg,  dba_tablespaces  ts
     where seg.segment_name    = p_segname
       and   seg.owner           = p_owner
       and   seg.tablespace_name = ts.tablespace_name; 
  end if;
  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,
       partition_name    => p_partition
     );
     --
     p( 'Free Blocks', l_free_blks );
  end if;
    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;
/

对于一般表：

SQL> set serverout on
SQL> exec show_space('T_CLM_MAIN','SCOTT');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................          35,457
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              85
Full Blocks        .....................          43,664
Total Blocks............................          79,616
Total Bytes.............................     652,214,272
Total MBytes............................             622
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               2
Last Used Ext BlockId...................          80,264
Last Used Block.........................             128

PL/SQL procedure successfully completed.

对于分区表：

SQL> exec show_space('PART_CLM_MAIN','SCOTT','TABLE PARTITION','DATA_PART2');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          80,140
Total Blocks............................          80,640
Total Bytes.............................     660,602,880
Total MBytes............................             630
Unused Blocks...........................              87
Unused Bytes............................         712,704
Last Used Ext FileId....................               7
Last Used Ext BlockId...................          80,520
Last Used Block.........................              41

PL/SQL procedure successfully completed.

对于分区索引：

SQL> exec show_space('LIDX_CLM_MAIN','SCOTT','INDEX PARTITION','LIDX_PART1');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             108
Total Blocks............................             128
Total Bytes.............................       1,048,576
Total MBytes............................               1
Unused Blocks...........................              14
Unused Bytes............................         114,688
Last Used Ext FileId....................              12
Last Used Ext BlockId...................               8
Last Used Block.........................             114

PL/SQL procedure successfully completed.

 

 

 

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

转载于:http://blog.itpub.net/228190/viewspace-875/