Skip Headers

Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)

Part Number A96612-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

DBMS_SPACE, 2 of 2


Summary of DBMS_SPACE Subprograms

Table 67-1 DBMS_SPACE Subprograms
Subprogram Description

UNUSED_SPACE Procedure

Returns information about unused space in an object (table, index, or cluster).

FREE_BLOCKS Procedure

Returns information about free blocks in an object (table, index, or cluster).

SPACE_USAGE Procedure

Returns information about free blocks in a bitmapped segment.

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 67-2 UNUSED_SPACE Procedure Parameters
Parameter Description

segment_owner

Schema name of the segment to be analyzed.

segment_name

Segment name of the segment to be analyzed.

segment_type

Type of the segment to be analyzed:

TABLE

TABLE PARTITION

TABLE SUBPARTITION

INDEX

INDEX PARTITION

INDEX SUBPARTITION

CLUSTER

LOB

total_blocks

Returns total number of blocks in the segment.

total_bytes

Returns total number of blocks in the segment, in bytes.

unused_blocks

Returns number of blocks which are not used.

unused_bytes

Returns, in bytes, number of blocks which are not used.

last_used_extent_ file_id

Returns the file ID of the last extent which contains data.

last_used_extent_ block_id

Returns the block ID of the last extent which contains data.

last_used_block

Returns the last block within this extent which contains data.

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.

FREE_BLOCKS Procedure

This procedure returns information about free blocks in an object (table, index, or cluster). See "SPACE_USAGE Procedure" for returning free block information in a bitmapped segment.

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 67-3 FREE_BLOCKS Procedure Parameters
Parameter Description

segment_owner

Schema name of the segment to be analyzed.

segment_name

Segment name of the segment to be analyzed.

segment_type

Type of the segment to be analyzed:

TABLE

TABLE PARTITION

TABLE SUBPARTITION

INDEX

INDEX PARTITION

INDEX SUBPARTITION

CLUSTER

LOB

freelist_group_id

Freelist group (instance) whose free list size is to be computed.

free_blks

Returns count of free blocks for the specified group.

scan_limit

Maximum number of free list blocks to read (optional).

Use a scan limit of X you are interested only in the question, "Do I have X blocks on the free list?"

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.

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

Note:

An error is raised if scan_limit is not a positive number.


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

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback