SPACE_USAGE Procedure

This procedure shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.

Syntax

DBMS_SPACE.SPACE_USAGE(
   segment_owner IN varchar2, 
   segment_name IN varchar2, 
   segment_type IN varchar2, 
   unformatted_blocks OUT number, 
   unformatted_bytes OUT number, 
   fs1_blocks OUT number, 
   fs1_bytes  OUT number, 
   fs2_blocks OUT number, 
   fs2_bytes  OUT number, 
   fs3_blocks OUT number, 
   fs3_bytes  OUT number, 
   fs4_blocks OUT number, 
   fs4_bytes  OUT number, 
   full_blocks OUT number, 
   full_bytes OUT number, 
   partition_name IN varchar2 DEFAULT NULL); 

Parameters

Table 60-4 SPACE_USAGE Procedure Parameters

Parameter	Description
segment_owner	Schema name of the segment to be analyzed
segment_name	Name of the segment to be analyzed
partition_name	Partition name of the segment to be analyzed
segment_type	Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
OUTPUT ARGUMENTS
unformatted_blocks	Total number of blocks that are unformatted
unformatted bytes	Total number of bytes that are unformatted
fs1_blocks	Number of blocks that has at least 0 to 25% free space
fs1_bytes	Number of bytes that has at least 0 to 25% free space
fs2_blocks	Number of blocks that has at least 25 to 50% free space
fs2_bytes	Number of bytes that has at least 25 to 50% free space
fs3_blocks	Number of blocks that has at least 50 to 75% free space
fs3_bytes	Number of bytes that has at least 50 to 75% free space
fs4_blocks	Number of blocks that has at least 75 to 100% free space
fs4_bytes	Number of bytes that has at least 75 to 100% free space
ful1_blocks	Total number of blocks that are full in the segment
full_bytes	Total number of bytes that are full in the segment

Example

variable unf number; 
variable unfb number; 
variable fs1 number; 
variable fs1b number; 
variable fs2 number; 
variable fs2b number; 
variable fs3 number; 
variable fs3b number; 
variable fs4 number; 
variable fs4b number; 
variable full number; 
variable fullb number; 

begin 
dbms_space.space_usage('U1','T', 
                        'TABLE', 
                        :unf, :unfb, 
                        :fs1, :fs1b, 
                        :fs2, :fs2b, 
                        :fs3, :fs3b, 
                        :fs4, :fs4b, 
                        :full, :fullb); 
end; 
/ 
print unf ; 
print unfb ; 
print fs4 ; 
print fs4b; 
print fs3 ; 
print fs3b; 
print fs2 ; 
print fs2b; 
print fs1 ; 
print fs1b; 
print full; 
print fullb;