show_space

CREATE OR REPLACE PROCEDURE SYS.SHOW_SPACE(p_segname_1 IN VARCHAR2,
                                       p_space     IN VARCHAR2 DEFAULT 'AUTO',
                                       p_type_1    IN VARCHAR2 DEFAULT 'TABLE',
                                       p_analyzed  IN VARCHAR2 DEFAULT 'N',
                                       p_owner_1   IN VARCHAR2 DEFAULT USER) AS
  p_segname VARCHAR2(100);
  p_type    VARCHAR2(10);
  p_owner   VARCHAR2(30);
  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); -- rainy changed
  p_owner   := upper(p_owner_1);
  p_type    := p_type_1;
  IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
    --rainy changed
    p_type := 'INDEX';
  END IF;
  IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
    --rainy changed
    p_type := 'TABLE';
  END IF;
  IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
    --rainy changed
    p_type := 'CLUSTER';
  END IF;
  dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname, segment_type => p_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);
  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,
                           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', l_total_bytes);
  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);
  /*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,
                           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', l_fs1_bytes);
    p('25% -- 50% free space blocks', l_fs2_blocks);
    p('25% -- 50% free space bytes', l_fs2_bytes);
    p('50% -- 75% free space blocks', l_fs3_blocks);
    p('50% -- 75% free space bytes', l_fs3_bytes);
    p('75% -- 100% free space blocks', l_fs4_blocks);
    p('75% -- 100% free space bytes', l_fs4_bytes);
    p('Unused Blocks', l_unformatted_blocks);
    p('Unused Bytes', l_unformatted_bytes);
    p('Total Blocks', l_full_blocks);
    p('Total bytes', l_full_bytes);
  END IF;
END;
/

GRANT EXECUTE ON show_space TO PUBLIC;
CREATE OR REPLACE Public SYNONYM  show_space FOR SYS.Show_Space;