show_space.sql

-- The SHOW_SPACE routine prints detailed space utilization information for database segments.
 desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT


--The arguments are as follows:
--• P_SEGNAME: Name of the segment—the table or index name, for example.
--• P_OWNER: Defaults to the current user, but you can use this routine to look at some other
--schema.
--• P_TYPE: Defaults to TABLE and represents the type of object you are looking at. For
--example, select distinct segment_type from dba_segments lists valid segment types.
--• P_PARTITION: Name of the partition when you show the space for a partitioned object.
--SHOW_SPACE shows space for only a partition at a time.
--The output of this routine looks as follows, when the segment resides in an Automatic Segment Space
--Management (ASSM) tablespace:


exec show_space('BIG_TABLE');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks ............................          75,681
Total Blocks............................          76,800
Total Bytes.............................     629,145,600
Total MBytes............................             600
Unused Blocks...........................             716
Unused Bytes............................       5,865,472
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          88,576
Last Used Block.........................             308

PL/SQL procedure successfully completed.

-- The items reported are as follows:
-- • Unformatted Blocks: The number of blocks that are allocated to the table below the high-water
-- mark, but have not been used. Add unformatted and unused blocks together to get a total
-- count of blocks allocated to the table but never used to hold data in an ASSM object.
-- • FS1 Blocks-FS4 Blocks: Formatted blocks with data. The ranges of numbers after their name
-- represent the emptiness of each block. For example, (0-25) is the count of blocks that are
-- between 0 and 25 percent empty.
-- • Full Blocks: The number of blocks that are so full that they are no longer candidates for future
-- inserts.
-- • Total Blocks, Total Bytes, Total Mbytes: The total amount of space allocated to the segment
-- measured in database blocks, bytes, and megabytes.
-- • Unused Blocks, Unused Bytes: Represents a portion of the amount of space never used. These
-- are blocks allocated to the segment, but are currently above the high-water mark of the
-- segment.
-- • Last Used Ext FileId: The file ID of the file that contains the last extent that contains data.
-- • Last Used Ext BlockId: The block ID of the beginning of the last extent; the block ID within the
-- last-used file.
-- • Last Used Block: The block ID offset of the last block used in the last extent.



-- show_space
CREATE OR REPLACE PROCEDURE show_space(
    p_segname   IN VARCHAR2,
    p_owner     IN VARCHAR2 DEFAULT USER,
    p_type      IN VARCHAR2 DEFAULT 'TABLE',
    p_partition IN VARCHAR2 DEFAULT NULL )
  -- this procedure uses authid current user so it can query DBA_*
  -- views using privileges from a ROLE and so it can be installed
  -- once per database, instead of once per user that wants to use it
  authid current_user
AS
  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;
  l_segment_space_mgmt VARCHAR2(255);
  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;
  -- inline procedure to print out numbers nicely formatted
  -- with a simple label
  PROCEDURE p(
      p_label IN VARCHAR2,
      p_num   IN NUMBER )
  IS
  BEGIN
    dbms_output.put_line( rpad(p_label,40,'.') || TO_CHAR(p_num,'999,999,999,999') );
  END;
BEGIN
  -- this query is executed dynamically in order to allow this procedure
  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
  -- via a role as is customary.
  -- NOTE: at runtime, the invoker MUST have access to these two
  -- views!
  -- this query determines if the object is an ASSM object or not
  BEGIN
    EXECUTE immediate 'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name' INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner;
  EXCEPTION
  WHEN too_many_rows THEN
    dbms_output.put_line ( 'This must be a partitioned table, use p_partition => ');
    RETURN;
  END;
  -- if the object is in an ASSM tablespace, we must use this API
  -- call to get space information, else we use the FREE_BLOCKS
  -- API for the user managed segments
  IF l_segment_space_mgmt = 'AUTO' THEN
    dbms_space.space_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
    p( 'Unformatted Blocks ', l_unformatted_blocks );
    p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
    p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
    p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
    p( 'FS4 Blocks (75-100)', l_fs4_blocks );
    p( 'Full Blocks ', l_full_blocks );
  ELSE
    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;
  -- and then the unused space API call to get the rest of the
  -- information
  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 );
  p( 'Total Blocks', l_total_blocks );
  p( 'Total Bytes', l_total_bytes );
  p( 'Total MBytes', TRUNC(l_total_bytes/1024/1024) );
  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 );
END;
/