show_space改良版，增加表分區(qū)索引分區(qū)自動識別

new_show_space.sql.zip

REM
REM   based on previous show_space script, now it can 
REM   identify all partition_name for table or index-
REM   automatically without specifying partition_name.
REM
REM   Usage:
REM      exec show_space('TABLE','OWNER','TABLE_NAME');
REM      or
REM      exec show_space('TABLE PARTITION','TEST','P_TAB','PART1');
REM
REM      exec show_space('INDEX','TEST','IDX_TAB');
REM      or
REM      exec show_space('INDEX PARTITION','TEST','IDX_TAB','IDX_PART1');
REM
REM   Edited by mx at 2020/03/27
REM   
-- based on previous procedure show_space from Internet.
set serveroutput on
CREATE 
  OR REPLACE PROCEDURE show_space (
    v_segment_type IN VARCHAR2 DEFAULT 'TABLE',
    v_segment_owner IN VARCHAR2 DEFAULT USER,
    v_segment_name IN VARCHAR2,
    v_partition_name IN VARCHAR2 DEFAULT NULL,
    v_space IN VARCHAR2 DEFAULT 'AUTO',
    v_analyzed IN VARCHAR2 DEFAULT 'Y' 
  ) AS 
p_segment_type VARCHAR2 ( 30 );
p_segment_owner VARCHAR2 ( 30 );
p_segment_name VARCHAR2 ( 50 );
p_partition_name VARCHAR2 ( 30 );
p_partitioned VARCHAR2 ( 5 );
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 print ( p_label IN VARCHAR2, p_num IN NUMBER ) IS BEGIN
  dbms_output.put_line ( rpad( p_label, 40, '.' ) || p_num );
  
END;
PROCEDURE analyze_space (
  f_segment_type IN VARCHAR2,
  f_segment_owner IN VARCHAR2,
  f_segment_name IN VARCHAR2,
  f_partition_name IN VARCHAR2 
  ) IS BEGIN
  dbms_space.unused_space (
    segment_owner => f_segment_owner,
    segment_name => f_segment_name,
    segment_type => f_segment_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,
    partition_name => f_partition_name 
  );
  IF
    v_space = 'MANUAL' 
    OR ( v_space <> 'auto' AND v_space <> 'AUTO' ) THEN
      dbms_space.free_blocks (
        segment_owner => f_segment_owner,
        segment_name => f_segment_name,
        segment_type => f_segment_type,
        freelist_group_id => 0,
        free_blks => l_free_blks,
        partition_name => f_partition_name 
      );
    print ( 'Free Blocks', l_free_blks );
    
  END IF;
  IF
    ( f_partition_name IS NULL ) THEN
      dbms_output.put_line (
        '--' || rpad( f_segment_owner || '.' || f_segment_name, 45, '-' ) 
      );
    ELSE dbms_output.put_line (
      '--' || rpad(
        f_segment_owner || '.' || f_segment_name || '.' || f_partition_name,
        45,
        '-' 
      ) 
    );
    
  END IF;
  print ( 'Total Blocks', l_total_blocks );
  print ( 'Total Bytes', l_total_bytes );
  print ( 'Unused Blocks', l_unused_blocks );
  print ( 'Unused Bytes', l_unused_bytes );
  print ( 'Last Used Ext FileId', l_LastUsedExtFileId );
  print ( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
  print ( 'Last Used Block', l_LAST_USED_BLOCK );
/*IF the segment is analyzed */
  IF
    v_analyzed = 'Y' THEN
      dbms_space.space_usage (
        segment_owner => f_segment_owner,
        segment_name => f_segment_name,
        segment_type => f_segment_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,
        partition_name => f_partition_name 
      );
    dbms_output.put_line ( 'The segment is analyzed.' );
    print ( '0% -- 25% free space blocks', l_fs1_blocks );
    print ( '0% -- 25% free space bytes', l_fs1_bytes );
    print ( '25% -- 50% free space blocks', l_fs2_blocks );
    print ( '25% -- 50% free space bytes', l_fs2_bytes );
    print ( '50% -- 75% free space blocks', l_fs3_blocks );
    print ( '50% -- 75% free space bytes', l_fs3_bytes );
    print ( '75% -- 100% free space blocks', l_fs4_blocks );
    print ( '75% -- 100% free space bytes', l_fs4_bytes );
    print ( 'Unused Blocks', l_unformatted_blocks );
    print ( 'Unused Bytes', l_unformatted_bytes );
    print ( 'Total Blocks', l_full_blocks );
    print ( 'Total bytes', l_full_bytes );
    dbms_output.put_line ( rpad( ' ', 48, '-' ) );
    
  END IF;
  
END;
BEGIN
  p_segment_name := upper( v_segment_name );
  p_segment_owner := upper( v_segment_owner );
  p_segment_type := upper( v_segment_type );
  p_partition_name := upper( v_partition_name );
IF
  ( v_segment_type = 'i' OR v_segment_type = 'I' ) THEN
    p_segment_type := 'INDEX';
    
  END IF;
  IF
    ( v_segment_type = 't' OR v_segment_type = 'T' ) THEN
      p_segment_type := 'TABLE';
    
  END IF;
  IF
    ( v_segment_type = 'c' OR v_segment_type = 'C' ) THEN
      p_segment_type := 'CLUSTER';
    
  END IF;
  SELECT
    partitioned INTO p_partitioned 
  FROM
    (
    SELECT
      partitioned 
    FROM
      all_tables 
    WHERE
      owner = p_segment_owner 
      AND table_name = p_segment_name UNION
    SELECT
      partitioned 
    FROM
      all_indexes 
    WHERE
      owner = p_segment_owner 
      AND index_name = p_segment_name 
    );
  IF
    ( p_segment_type = 'TABLE' AND p_partitioned = 'YES' ) THEN
      p_segment_type := 'TABLE PARTITION';
    FOR t IN (
      SELECT
        partition_name 
      FROM
        all_tab_partitions 
      WHERE
        table_owner = p_segment_owner 
        AND table_name = p_segment_name 
      ORDER BY
        to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) ) 
      )
      loop
      analyze_space ( p_segment_type, p_segment_owner, p_segment_name, t.partition_name );
    
  END loop;
ELSIF ( p_segment_type = 'INDEX' AND p_partitioned = 'YES' ) THEN
  p_segment_type := 'INDEX PARTITION';
  FOR i IN (
    SELECT
      partition_name 
    FROM
      all_tab_partitions 
    WHERE
      table_owner = p_segment_owner 
      AND table_name = p_segment_name 
    ORDER BY
      to_number( regexp_substr( partition_name, '[0-9]*[0-9]', 1 ) ) 
    )
    loop
    analyze_space ( p_segment_type, p_segment_owner, p_segment_name, i.partition_name );
  
END loop;
ELSE analyze_space ( p_segment_type, p_segment_owner, p_segment_name, p_partition_name );
END IF;
EXCEPTION
  WHEN others THEN
  dbms_output.put_line('Usage:');
  dbms_output.put_line('- exec show_space(''table'',''owner'',''table_name'');');
  dbms_output.put_line('- exec show_space(''table partition'',''owner'',''table_name'',''partition_name'');');
  dbms_output.put_line('- exec show_space(''index'',''owner'',''index_name'');');
  dbms_output.put_line('- exec show_space(''index partition'',''owner'',''index_name'',''partition_name'');');
END;
/