Chapter 33. DBMS

Chapter 33. DBMS_SPACE

Table of Contents

33.1. Overview

33.2. Procedures

33.2.1. SPACE_USAGE
33.2.2. UNUSED_SPACE

This chapter briefly introduces the DBMS_SPACE package, and describes how to use the procedures of the package.

33.1. Overview

DBMS_SPACE provides information about segment sizes and space usage.

33.2. Procedures

This section describes the procedures provided by the DBMS_SPACE package, in alphabetical order.

33.2.1. SPACE_USAGE

Displays information about the space usage under the High Water Mark (HWM) of the segment. Information about bitmap blocks, segment header, and extent map blocks are not displayed.

Note

The High Water Mark is the space where tables, indexes, and clusters have not yet been used since they were created.

Details about the SPACE_USAGE procedure are as follows:

Prototype

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
);

Parameter

Parameter	Description
segment_owner	Schema object owner.
segment_name	Schema object name.
segment_type	Schema object type. One of: TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION
unformatted_blocks	Number of unformatted blocks under the HWM.
unformatted_bytes	Number of bytes of formatted blocks under the HWM.
fs1_blocks	Number of blocks with 0 to 25% free space.
fs1_bytes	Number of bytes of blocks with 0 to 25% free space.
fs2_blocks	Number of blocks with 25 to 50% free space.
fs2_bytes	Number of bytes of blocks with 25 to 50% free space.
fs3_blocks	Number of blocks with 50 to 75% free space.
fs3_bytes	Number of bytes of blocks with 50 to 75% free space.
fs4_blocks	Number of blocks with 75 to 100% free space.
fs4_bytes	Number of bytes of blocks with 75 to 100% free space.
full_blocks	Number of blocks marked as full.
full_bytes	Number of bytes of blocks marked as full.
partition_name	Partition name defines: Partition name, if a partition. Subpartition name, if a composite partition.

Example

set serveroutput on;

DECLARE
  unformatted_blocks NUMBER;
  unformatted_bytes  NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;

BEGIN
  DBMS_SPACE.SPACE_USAGE('SYS', 'EMP', 'TABLE'
                         ,unformatted_blocks
                         ,unformatted_bytes
                         ,fs1_blocks
                         ,fs1_bytes
                         ,fs2_blocks
                         ,fs2_bytes
                         ,fs3_blocks
                         ,fs3_bytes
                         ,fs4_blocks
                         ,fs4_bytes
                         ,full_blocks
                         ,full_bytes);
  DBMS_OUTPUT.PUT_LINE('Space utilization (TABLES) ');
  DBMS_OUTPUT.PUT_LINE('unformatted_blocks: '|| TO_CHAR(unformatted_blocks) );
  DBMS_OUTPUT.PUT_LINE('unformatted_bytes: '|| TO_CHAR(unformatted_bytes) );
  DBMS_OUTPUT.PUT_LINE('fs1_blocks: '|| TO_CHAR(fs1_blocks) );
  DBMS_OUTPUT.PUT_LINE('fs1_bytes: '|| TO_CHAR(fs1_bytes) );
  DBMS_OUTPUT.PUT_LINE('fs2_blocks: '|| TO_CHAR(fs2_blocks) );
  DBMS_OUTPUT.PUT_LINE('fs2_bytes: '|| TO_CHAR(fs2_bytes) );
  DBMS_OUTPUT.PUT_LINE('fs3_blocks: '|| TO_CHAR(fs3_blocks) );
  DBMS_OUTPUT.PUT_LINE('fs3_bytes: '|| TO_CHAR(fs3_bytes) );
  DBMS_OUTPUT.PUT_LINE('fs4_blocks: '|| TO_CHAR(fs4_blocks) );
  DBMS_OUTPUT.PUT_LINE('fs4_bytes: '|| TO_CHAR(fs4_bytes) );
  DBMS_OUTPUT.PUT_LINE('full_blocks: '|| TO_CHAR(full_blocks) );
  DBMS_OUTPUT.PUT_LINE('full_bytes: '|| TO_CHAR(full_bytes) );
END;

33.2.2. UNUSED_SPACE

Provides information about the space usage above the HWM of the segment.

Details about the UNUSED_SPACE procedure are as follows:

Prototype

DBMS_SPACE.UNUSED_SPACE
(
    segment_owner              IN  VARCHAR2,
    segment_name               IN  VARCHAR2,
    segment_type               IN  VARCHAR2,
    total_blocks               OUT NUMBER,
    total_bytes                OUT NUMBER,
    unused_blocks              OUT NUMBER,
    unused_bytes               OUT NUMBER,
    last_used_extent_file_id   OUT NUMBER,
    last_used_extent_block_id  OUT NUMBER,
    last_used_block            OUT NUMBER,
    partition_name             IN  VARCHAR2 DEFAULT NULL
);

Parameter

Parameter	Description
segment_owner	Schema object owner.
segment_name	Schema object name.
segment_type	Schema object type. One of: TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION
total_blocks	Total number of blocks in the segment.
total_bytes	Total number of bytes of blocks in the segment.
unused_blocks	Number of blocks above the HWM in the segment.
unused_bytes	Number of bytes of blocks above the HWM in the segment.
last_used_extent_file_id	File ID of the extent that was last used for INSERT.
last_used_extent_block_id	Starting block ID of the extent that was last used for INSERT. If combined with the file ID, it becomes the Data Block Address.
last_used_extent_block	Number (offset in the extent) of the block last used for INSERT.
partition_name	Partition name defines: Partition name, if a partition. Subpartition name, if a composite partition.

Example

set serveroutput on;

DECLARE
  total_blocks   NUMBER;
  total_bytes    NUMBER;
  unused_blocks  NUMBER;
  unused_bytes   NUMBER;
  last_used_extent_file_id   NUMBER;
  last_used_extent_block_id  NUMBER;
  last_used_block            NUMBER;

BEGIN
  DBMS_SPACE.UNUSED_SPACE('SYS', 'EMP', 'TABLE'
                          ,total_blocks
                          ,total_bytes
                          ,unused_blocks
                          ,unused_bytes
                          ,last_used_extent_file_id
                          ,last_used_extent_block_id
                          ,last_used_block);

  DBMS_OUTPUT.PUT_LINE('Unused space utilization (TABLES) ');
  DBMS_OUTPUT.PUT_LINE('total_blocks: '|| TO_CHAR(total_blocks) );
  DBMS_OUTPUT.PUT_LINE('total_bytes: '|| TO_CHAR(total_bytes) );
  DBMS_OUTPUT.PUT_LINE('unused_blocks: '|| TO_CHAR(unused_blocks) );
  DBMS_OUTPUT.PUT_LINE('unused_bytes: '|| TO_CHAR(unused_bytes) );
  DBMS_OUTPUT.PUT_LINE('last_used_extent_file_id: '|| 
                        TO_CHAR(last_used_extent_file_id) );
  DBMS_OUTPUT.PUT_LINE('last_used_extent_block_id: '|| 
                        TO_CHAR(last_used_extent_block_id) );
  DBMS_OUTPUT.PUT_LINE('last_used_block: '|| TO_CHAR(last_used_block) );
END;

Prev		Next
Chapter 32. DBMS_SESSION	Home	Chapter 34. DBMS_SPACE_ADMIN