Oracle Database - DBMS_SPACE Package

> Database > Oracle Database

1 - About

Advertising

3 - Space Usage

3.1 - Sqlplus

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('QS_DWH','SYS_IL0000084970C00036$$', 
                        'LOBINDEX', 
                        :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; 

4 - unused_space

The UNUSED_SPACE procedure of the DBMS_SPACE package returns space information on a segment:

  • the position of the high water mark (ie the last block within the extent which contains data)
  • and the amount of unused space

4.1 - Plsql

DECLARE
   in_owner                       dba_segments.owner%TYPE :='QS_DWH';
   in_segment_name                dba_segments.segment_name%TYPE:='SYS_IL0000084970C00036$$';
   in_segment_type                dba_segments.segment_type%TYPE:='LOBINDEX';
   in_partition_name              dba_segments.partition_name%TYPE := NULL;
 
 
   out_total_blocks               INTEGER;
   out_total_bytes                INTEGER;
   out_unused_blocks              INTEGER;
   out_unused_bytes               INTEGER;
   out_last_used_extent_file_id   INTEGER;
   out_last_used_extent_block_id  INTEGER;
   out_last_used_block            INTEGER;
 
BEGIN
DBMS_SPACE.unused_space(
    segment_owner              =>  in_owner
    ,segment_name              =>  in_segment_name
    ,segment_type              =>  in_segment_type
    ,partition_name            =>  in_partition_name
    ,total_blocks              =>  out_total_blocks
    ,total_bytes               =>  out_total_bytes 
    ,unused_blocks             =>  out_unused_blocks
    ,unused_bytes              =>  out_unused_bytes
    ,last_used_extent_file_id  =>  out_last_used_extent_file_id
    ,last_used_extent_block_id =>  out_last_used_extent_block_id
    ,last_used_block           =>  out_last_used_block
    );
 
DBMS_OUTPUT.PUT_LINE('Input Parameters:');
DBMS_OUTPUT.PUT_LINE('Owner:                    ' ||  in_owner);
DBMS_OUTPUT.PUT_LINE('Segment Name:             ' ||  in_segment_name);
DBMS_OUTPUT.PUT_LINE('Segment Type:             ' ||  in_segment_type);
DBMS_OUTPUT.PUT_LINE('Partition Name:           ' ||  in_partition_name || 'Partition name of the segment to be analyzed. This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.');
 
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Output parameters:');
DBMS_OUTPUT.PUT_LINE('Total Blocks:             ' ||  out_total_blocks || ' (number of blocks in the segment)');
DBMS_OUTPUT.PUT_LINE('Total Bytes:              ' ||  out_total_bytes || ' (number of blocks in the segment, in bytes)');
DBMS_OUTPUT.PUT_LINE('Unused Blocks:            ' ||  out_unused_blocks || '(number of blocks which are not used.)');
DBMS_OUTPUT.PUT_LINE('Unused Bytes:             ' ||  out_unused_bytes || '(number of blocks which are not used in bytes)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent File Id: ' ||  out_last_used_extent_file_id || '(Returns the file ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent Block Id:' ||  out_last_used_extent_block_id || '(Returns the starting block ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Block:          ' ||  out_last_used_block || '(Returns the last block within this extent which contains data)');
END;
/

4.2 - sqlplus

VARIABLE total_blocks NUMBER;
VARIABLE total_bytes NUMBER;
VARIABLE unused_blocks NUMBER;
VARIABLE unused_bytes NUMBER;
VARIABLE lastextf NUMBER;
VARIABLE last_extb NUMBER;
VARIABLE lastusedblock NUMBER;
BEGIN
DBMS_SPACE.UNUSED_SPACE('QS_DWH', 'SYS_IL0000084970C00036$$', 'LOBINDEX', :total_blocks, 
   :total_bytes,:unused_blocks, :unused_bytes, :lastextf,  
   :last_extb, :lastusedblock); 
DBMS_OUTPUT.PUT_LINE('Output parameters:');
DBMS_OUTPUT.PUT_LINE('Total Blocks:             ' || :total_blocks || ' (number of blocks in the segment)');
DBMS_OUTPUT.PUT_LINE('Total Bytes:              ' || :total_bytes || ' (number of blocks in the segment, in bytes)');
DBMS_OUTPUT.PUT_LINE('Unused Blocks:            ' || :unused_blocks || '(number of blocks which are not used.)');
DBMS_OUTPUT.PUT_LINE('Unused Bytes:             ' || :unused_bytes || '(number of blocks which are not used in bytes)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent File Id: ' || :lastextf || '(Returns the file ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Extent Block Id:' || :last_extb || '(Returns the starting block ID of the last extent which contains data.)');
DBMS_OUTPUT.PUT_LINE('Last Used Block:          ' || :lastusedblock || '(Returns the last block within this extent which contains data)');
END;
/

5 - Documentation / Reference

db/oracle/dbms_space.txt · Last modified: 2017/09/06 19:30 by gerardnico