show_space 参考

仅供参考

CREATE OR REPLACE PROCEDURE show_space(p_segname_1 IN VARCHAR2,
                                       p_type_1 IN VARCHAR2 DEFAULT 'TABLE',
                                       p_space IN VARCHAR2 DEFAULT 'AUTO',
                                       p_analyzed IN VARCHAR2 DEFAULT 'N',
                                       p_partition_1 IN VARCHAR2 DEFAULT NULL,
                                       p_owner_1 IN VARCHAR2 DEFAULT USER)
  AUTHID CURRENT_USER AS
  /*
  -- grant execute on sys.DBMS_SPACE to scott;  -- 普通用户
  -- exec dbms_stats.gather_table_stats('SCOTT','EMP')  -- 提前收集统计信息
  --  exec show_space('EMP', 'TABLE', 'AUTO', 'Y', null, 'SCOTT')
  */
  p_segname   VARCHAR2(100);
  p_type      VARCHAR2(30);
  p_owner     VARCHAR2(30);
  p_partition VARCHAR2(50);

  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);
  p_owner     := UPPER(p_owner_1);
  p_type      := p_type_1;
  p_partition := UPPER(p_partition_1);

  IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
    p_type := 'INDEX';
  END IF;

  IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
    p_type := 'TABLE';
  END IF;

  IF (p_type_1 = 'tp' OR p_type_1 = 'TP') THEN
    p_type := 'TABLE PARTITION';
  END IF;

  IF (p_type_1 = 'ip' OR p_type_1 = 'IP') THEN
    p_type := 'INDEX PARTITION';
  END IF;

  IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
    p_type := 'CLUSTER';
  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);

  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,
                           partition_name => p_partition,
                           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(MB)', round(l_total_bytes/1024/1024,1));
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes(MB)', round(l_unused_bytes/1024/1024,1));
  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,
                           partition_name => p_partition,
                           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(MB)', round(l_fs1_bytes/1024/1024,1));
    p('25% -- 50% free space blocks', l_fs2_blocks);
    p('25% -- 50% free space bytes(MB)', round(l_fs2_bytes/1024/1024,1));
    p('50% -- 75% free space blocks', l_fs3_blocks);
    p('50% -- 75% free space bytes(MB)', round(l_fs3_bytes/1024/1024,1));
    p('75% -- 100% free space blocks', l_fs4_blocks);
    p('75% -- 100% free space bytes(MB)', round(l_fs4_bytes/1024/1024,1));
    p('Unused Blocks', l_unformatted_blocks);
    p('Unused Bytes(MB)', round(l_unformatted_bytes/1024/1024,1));
    p('Total Blocks', l_full_blocks);
    p('Total bytes(MB)', round(l_full_bytes/1024/1024,1));
  END IF;
END;
/

效果图


效果图2