show_space改良版，增加表分区索引分区自动识别

new_show_space.sql.zip

REMREM   based on previous show_space script, now it can REM   identify all partition_name for table or index-REM   automatically without specifying partition_name.REMREM   Usage:REM      exec show_space('TABLE','OWNER','TABLE_NAME');REM      orREM      exec show_space('TABLE PARTITION','TEST','P_TAB','PART1');REMREM      exec show_space('INDEX','TEST','IDX_TAB');REM      orREM      exec show_space('INDEX PARTITION','TEST','IDX_TAB','IDX_PART1');REMREM   Edited by mx at 2020/03/27REM   -- based on previous procedure show_space from Internet.set serveroutput onCREATE   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;/