46
DBMS_SPACE

The DBMS_SPACE package enables you to analyze segment growth and space requirements.

Security

This package runs with SYS privileges.

Requirements

The execution privilege is granted to PUBLIC. Subprograms in this package run under the caller security. The user must have ANALYZE privilege on the object.

Summary of Subprograms

Table 46-1 DBMS_ALERT Package Subprograms

UNUSED_SPACE procedure

FREE_BLOCKS procedure

UNUSED_SPACE procedure

This procedure returns information about unused space in an object (table, index, or cluster).

Syntax

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

Parameters

Table 46-2 UNUSED_SPACE Procedure Parameters

segment_owner

segment_name

segment_type

TABLE 
TABLE PARTITION 
TABLE SUBPARTITION 
INDEX 
INDEX PARTITION 
INDEX SUBPARTITION 
CLUSTER 
LOB 

total_blocks

total_bytes

unused_blocks

unused_bytes

last_used_extent_ file_
id

last_used_extent_ 
block_id

last_used_block

partition_name

FREE_BLOCKS procedure

This procedure returns information about free blocks in an object (table, index, or cluster).

Syntax

DBMS_SPACE.FREE_BLOCKS (
   segment_owner     IN  VARCHAR2, 
   segment_name      IN  VARCHAR2,
   segment_type      IN  VARCHAR2,
   freelist_group_id IN  NUMBER,
   free_blks         OUT NUMBER,
   scan_limit        IN  NUMBER DEFAULT NULL,
   partition_name    IN  VARCHAR2 DEFAULT NULL);

Pragmas

pragma restrict_references(free_blocks,WNDS);

Parameters

Table 46-3 FREE_BLOCKS Procedure Parameters

segment_owner

segment_name

segment_type

TABLE 
TABLE PARTITION 
TABLE SUBPARTITION 
INDEX 
INDEX PARTITION 
INDEX SUBPARTITION 
CLUSTER 
LOB 

freelist_group_id

free_blks

scan_limit

partition_name

Examples

Example 1

The following declares the necessary bind variables and executes.

DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, 
   :total_bytes,:unused_blocks, :unused_bytes, :lastextf,  
   :last_extb, :lastusedblock); 

This fills the unused space information for bind variables in EMP table in SCOTT schema.

Example 2

The following uses the CLUS cluster in SCOTT schema with 4 freelist groups. It returns the number of blocks in freelist group 3 in CLUS.

DBMS_SPACE.FREE_BLOCKS('SCOTT', 'CLUS', 'CLUSTER', 3, :free_blocks);