show_space脚本

CREATE OR REPLACE PROCEDURE show_space(
  --this procedure can only be used for non-partitioned tables,indexex,clusters
  seg_name VARCHAR2
) AS
  blks_1       NUMBER;
  bytes_1      NUMBER;
  blks_2       NUMBER;
  bytes_2      NUMBER;
  blks_3       NUMBER;
  bytes_3      NUMBER;
  blks_4       NUMBER;
  bytes_4      NUMBER;
  blks_f       NUMBER;
  bytes_f      NUMBER;
  blks_u       NUMBER;
  bytes_u      NUMBER;
  tblks        NUMBER;
  tbs          NUMBER;
  ublks        NUMBER;
  ubs          NUMBER;
  luefid       NUMBER;
  luebid       NUMBER;
  lastub       NUMBER;
  free_blks     NUMBER;
  isassm       VARCHAR2(10);
  seg_type     VARCHAR2(10);
  PROCEDURE output(
    in_str   VARCHAR2
   ,in_num   NUMBER
   ,isformat NUMBER DEFAULT 0
  ) IS
    format_num VARCHAR2(20);
  BEGIN
    IF isformat = 0 THEN
      dbms_output.put_line(rpad(in_str, 35, '-') || '> ' || in_num);
    ELSE
      IF in_num / 1024 / 1024 < 1 AND in_num / 1024 / 1024 <> 0 THEN
        format_num := '0' || to_char(round(in_num / 1024 / 1024, 2));
      ELSE
        format_num := to_char(round(in_num / 1024 / 1024, 2));
      END IF;
      dbms_output.put_line(rpad(in_str, 35, '-') || '> ' || format_num ||
                           '(m)');
    END IF;
  END;

BEGIN
  --1.ensure segment_type
  SELECT a.segment_type
  INTO seg_type
  FROM dba_segments a
  WHERE a.segment_name = upper(seg_name);
  --2.ensure segment_space_management
  --(1).if it is table
  IF upper(seg_type)=upper('table') THEN
    SELECT a.segment_space_management
    INTO isassm
    FROM dba_tablespaces a, dba_tables b
    WHERE b.table_name = upper(seg_name)
          AND a.tablespace_name = b.tablespace_name;
  --(2).if it is index
  ELSIF upper(seg_type)=upper('index') THEN
    SELECT a.segment_space_management
    INTO isassm
    FROM dba_tablespaces a, dba_indexes b
    WHERE b.index_name = upper(seg_name)
          AND a.tablespace_name = b.tablespace_name;
  --(3).if it is cluster
  ELSIF upper(seg_type)=upper('cluster') THEN
    SELECT a.segment_space_management
    INTO isassm
    FROM dba_tablespaces a, dba_clusters b
    WHERE b.cluster_name = upper(seg_name)
          AND a.tablespace_name = b.tablespace_name;
  END IF;
  dbms_output.put_line('');
  dbms_output.put_line('## '||'the tablespace of this segment is ' || upper(isassm) || ' ##');
  dbms_output.put_line('');
  --3.dbms_space.unused_space
  dbms_space.unused_space(
    segment_owner             => upper(USER),
    segment_name              => upper(seg_name),
    segment_type              => upper(seg_type),
    total_blocks              => tblks,
    total_bytes               => tbs,
    unused_blocks             => ublks,
    unused_bytes              => ubs,
    last_used_extent_file_id  => luefid,
    last_used_extent_block_id => luebid,
    last_used_block           => lastub
  );
  output('total_blocks', tblks);
  output('total_bytes', tbs, 1);
  output('unused_blocks', ublks);
  output('unused_bytes', ubs, 1);
  output('last_used_extent_file_id', luefid);
  output('last_used_extent_block_id', luebid);
  output('last_used_block', lastub);
  --4.dbms_space.space_usage,this can only be used for assm
  IF upper(isassm) = upper('auto') THEN
      dbms_space.space_usage(
        segment_owner      => upper(USER),
        segment_name       => upper(seg_name),
        segment_type       => upper(seg_type),
        unformatted_blocks => blks_u,
        unformatted_bytes  => bytes_u,
        fs1_blocks         => blks_1,
        fs2_blocks         => blks_2,
        fs3_blocks         => blks_3,
        fs4_blocks         => blks_4,
        fs1_bytes          => bytes_1,
        fs2_bytes          => bytes_2,
        fs3_bytes          => bytes_3,
        fs4_bytes          => bytes_4,
        full_blocks        => blks_f,
        full_bytes         => bytes_f
      );
      output('unformatted_blocks', blks_u);
      output('unformatted_bytes', bytes_u, 1);
      output(' 0% --  25% free space blocks', blks_1);
      output(' 0% --  25% free space bytes', bytes_1, 1);
      output('25% --  50% free space blocks', blks_2);
      output('25% --  50% free space bytes', bytes_2, 1);
      output('50% --  75% free space blocks', blks_3);
      output('50% --  75% free space bytes', bytes_3, 1);
      output('75% -- 100% free space blocks', blks_4);
      output('75% -- 100% free space bytes', bytes_4, 1);
      output('full_blocks', blks_f);
      output('full_bytes', bytes_f, 1);
  END IF;
  --5.dbms_space.free_blocks,this can only be used for MSSM
  IF upper(isassm) = upper('manual') THEN
    dbms_space.free_blocks(
      segment_owner     => upper(USER),
      segment_name      => upper(seg_name),
      segment_type      => upper(seg_type),
      freelist_group_id => 0,
      free_blks         => free_blks
    );
    output('free_blocks',free_blks);
  END IF;
END;

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

转载于:http://blog.itpub.net/22745099/viewspace-625715/