Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

98 DBMS_SPACE

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

This chapter contains the following topics:


Using DBMS_SPACE


Security Model

This package runs with SYS privileges. 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.


Data Structures

The DBMS_SPACE package defines a RECORD type and a TABLE type.

RECORD Types

ASA_RECO_ROW Record Type

TABLE Types

ASA_RECO_ROW_TB Table Type


ASA_RECO_ROW Record Type

This type contains the column type of individual columns returned by the ASA_RECOMMENDATIONS Function.

Syntax

TYPE asa_reco_row IS RECORD (
   tablespace_name       VARCHAR2(30),
   segment_owner         VARCHAR2(30),
   segment_name          VARCHAR2(30),   segment_type          VARCHAR2(18),
   partition_name        VARCHAR2(30),
   allocated_space       NUMBER,
   used_space            NUMBER,
   reclaimable_space     NUMBER,
   chain_rowexcess       NUMBER,
   recommendations       VARCHAR2(1000),
   c1                    VARCHAR2(1000),
   c2                    VARCHAR2(1000),
   c3                    VARCHAR2(1000),
   task_id               NUMBER,
   mesg_id               NUMBER);

Attributes

Table 98-1 ASA_RECO_ROW Attributes

Field Description
tablespace_name Name of the tablespace containing the object
segment_owner Name of the schema
segment_name Name of the object
segment_type Type of the segment 'TABLE','INDEX' and so on
partition_name Name of the partition
allocated_space Space allocated to the segment
used_space Space actually used by the segment
reclaimable_space Reclaimable free space in the segment
chain_rowexcess Percentage of excess chain row pieces that can be eliminated
recommendations Recommendation or finding for this segment
c1 Command associated with the recommendation
c2 Command associated with the recommendation
c3 Command associated with the recommendation
task_id Advisor Task that processed this segment
mesg_id Message ID corresponding to the recommendation


ASA_RECO_ROW_TB Table Type

Syntax

TYPE asa_reco_row_tb IS TABLE OF asa_reco_row;

Summary of DBMS_SPACE Subprograms

Table 98-2 DBMS_SPACE Package Subprograms

Subprogram Description
ASA_RECOMMENDATIONS Function
Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user
CREATE_INDEX_COST Procedure
Determines the cost of creating an index on an existing table
CREATE_TABLE_COST Procedures
Determines the size of the table given various attributes
FREE_BLOCKS Procedure
Returns information about free blocks in an object (table, index, or cluster)
OBJECT_DEPENDENT_SEGMENTS Function
Returns the list of segments that are associated with the object
OBJECT_GROWTH_TREND Function
A table function where each row describes the space usage of the object at a specific point in time
SPACE_USAGE Procedure
Returns information about free blocks in an auto segment space managed segment
UNUSED_SPACE Procedure
Returns information about unused space in an object (table, index, or cluster)


ASA_RECOMMENDATIONS Function

This function returns returns recommendations using the stored results of the auto segment advisor. This function returns results from the latest run on any given object.

Syntax

DBMS_SPACE.ASA_RECOMMENDATIONS (
   all_runs        IN    VARCHAR2 DEFAULT := TRUE,
   show_manual     IN    VARCHAR2 DEFAULT := TRUE,
   show_findings   IN    VARCHAR2 DEFAULT := FALSE)
 RETURN ASA_RECO_ROW_TB PIPELINED;

Parameters

Table 98-3 CREATE_INDEX_COST Procedure Parameters

Parameter Description
all_runs If TRUE, returns recommendations/findings for all runs of auto segment advisor. If FALSE, returns the results of the LATEST run only. LATEST does not make sense for manual invocation of segment advisor. This is applicable only for auto advisor.
show_manual If TRUE, we show the results of manual invocations only. The auto advisor results are excluded. If FALSE, results of manual invocation of segment advisor are not returned.
show_findings Show only the findings instead of the recommendations


CREATE_INDEX_COST Procedure

This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Syntax

DBMS_SPACE.CREATE_INDEX_COST (
   ddl             IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);

Pragmas

pragma restrict_references(create_index_cost,WNDS);

Parameters

Table 98-4 CREATE_INDEX_COST Procedure Parameters

Parameter Description
ddl The create index DDL statement
used_bytes The number of bytes representing the actual index data
alloc_bytes Size of the index when created in the tablespace
plan_table Which plan table to use, default NULL

Usage Notes


CREATE_TABLE_COST Procedures

This procedure is used in capacity planning to determine the size of the table given various attributes. The size of the object can vary widely based on the tablespace storage attributes, tablespace block size, and so on. There are two overloads of this procedure.

This procedure can be used on tablespace of dictionary managed and locally managed extent management as well as manual and auto segment space management.

Syntax

DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   avg_row_size       IN NUMBER,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   colinfos           IN CREATE_TABLE_COST_COLUMNS,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

CREATE TYPE create_table_cost_colinfo IS OBJECT (
   COL_TYPE   VARCHAR(200),
   COL_SIZE   NUMBER);

Parameters

Table 98-5 CREATE_TABLE_COST Procedure Parameters

Parameter Description
tablespace_name The tablespace in which the object will be created. The default is SYSTEM tablespace.
avg_row_size The anticipated average row size in the table
colinfos The description of the columns
row_count The anticipated number of rows in the table
pct_free The percentage of free space in each block for future expansion of existing rows due to updates
used_bytes The space used by user data
alloc_bytes The size of the object taking into account the tablespace extent characteristics

Usage Notes

Examples

-- review the parameters 
SELECT argument_name, data_type, type_owner, type_name 
FROM all_arguments 
WHERE object_name = 'CREATE_TABLE_COST' 
AND overload = 2 
 
-- examine the input parameter type 
SELECT text 
FROM dba_source 
WHERE name = 'CREATE_TABLE_COST_COLUMNS'; 
 
-- drill down further into the input parameter type 
SELECT text 
FROM dba_source 
WHERE name = 'create_table_cost_colinfo'; 
 
set serveroutput on 
 
DECLARE 
 ub NUMBER; 
 ab NUMBER; 
 cl sys.create_table_cost_columns; 
BEGIN 
  cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10), 
        sys.create_table_cost_colinfo('VARCHAR2',30), 
        sys.create_table_cost_colinfo('VARCHAR2',30), 
        sys.create_table_cost_colinfo('DATE',NULL)); 
 
  DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab); 
 
  DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab)); 
END; 
/ 

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 an auto segment space managed 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 98-6 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, INDEX, or CLUSTER):
  • TABLE

  • TABLE PARTITION

  • TABLE SUBPARTITION

  • INDEX

  • INDEX PARTITION

  • INDEX SUBPARTITION

  • CLUSTER

  • LOB

  • LOB PARTITION

  • LOB SUBPARTITION

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 composite.


Examples

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.

OBJECT_DEPENDENT_SEGMENTS Function

This table function, given an object, returns the list of segments that are associated with the object.

Syntax

DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
   objowner    IN     VARCHAR2,
   objname     IN     VARCHAR2,
   partname    IN     VARCHAR2,
   objtype     IN     NUMBER) 
  RETURN dependent_segments_table PIPELINED; 

Parameters

Table 98-7 OBJECT_DEPENDENT_SEGMENTS Function Parameters

Parameter Description
objowner The schema containing the object
objname The name of the object
partname The name of the partition
objtype Type of the object:
  • OBJECT_TYPE_TABLE constant positive := 1;

  • OBJECT_TYPE_NESTED_TABLE constant positive := 2;

  • OBJECT_TYPE_INDEX constant positive := 3;

  • OBJECT_TYPE_CLUSTER constant positive := 4;

  • OBJECT_TYPE_TABLE_PARTITION constant positive := 7;

  • OBJECT_TYPE_INDEX_PARTITION constant positive := 8;

  • OBJECT_TYPE_TABLE_SUBPARTITION constant positive := 9;

  • OBJECT_TYPE_INDEX_SUBPARTITION constant positive := 10;

  • OBJECT_TYPE_MV constant positive := 13;

  • OBJECT_TYPE_MVLOG constant positive := 14;


Return Values

The content of one row of a dependent_segments_table:

TYPE object_dependent_segment IS RECORD (
   segment_owner      VARCHAR2(100),
   segment_name       VARCHAR2(100),
   segment_type       VARCHAR2(100),
   tablespace_name    VARCHAR2(100),
   partition_name     VARCHAR2(100),
   lob_column_name    VARCHAR2(100)); 

Table 98-8 OBJECT_DEPENDENT_SEGMENT Type Parameters

Parameter Description
segment_owner The schema containing the segment
segment_name The name of the segment
segmemnt_type The type of the segment, such as table, index or LOB
tablespace_name The name of the tablespace
partition_name The name of the partition, if any
lob_column_name The name of the LOB column, if any


OBJECT_GROWTH_TREND Function

This is a table function. The output will be in the form of one or more rows where each row describes the space usage of the object at a specific point in time. Either the space usage totals will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF.

Syntax

DBMS_SPACE.OBJECT_GROWTH_TREND (
   object_owner           IN    VARCHAR2,
   object_name            IN    VARCHAR2,
   object_type            IN    VARCHAR2,
   partition_name         IN    VARCHAR2 DEFAULT NULL,
   start_time             IN    TIMESTAMP DEFAULT NULL,
   end_time               IN    TIMESTAMP DEFAULT NULL,
   interval               IN    DSINTERVAL_UNCONSTRAINED DEFAULT NULL,
   skip_interpolated      IN    VARCHAR2 DEFAULT 'FALSE',
   timeout_seconds        IN    NUMBER DEFAULT NULL,
   single_datapoint_flag  IN    VARCHAR2 DEFAULT 'TRUE') 
 RETURN object_growth_trend_table PIPELINED;  

Parameters

Table 98-9 OBJECT_GROWTH_TREND Function Parameters

Parameter Description
object_owner The schema containing the object
object_name The name of the object
object_type The type of the object
partition_name The name of the partition
start_time Statistics generated after this time will be used in generating the growth trend
end_time Statistics generated until this time will be used in generating the growth trend
interval The interval at which to sample
skip_interpolated Whether interpolation of missing values should be skipped
timeout_seconds The time-out value for the function in seconds
single_data_point_flag Whether in the absence of statistics the segment should be sampled

Return Values

The object_growth_trend_row and object_growth_trend_table are used by the OBJECT_GROWTH_TREND table function to describe its output.

TYPE object_growth_trend_row IS RECORD(
   timepoint      TIMESTAMP,
   space_usage    NUMBER,
   space_alloc    NUMBER,
   quality        VARCHAR(20)); 

Table 98-10 OBJECT_GROWTH_TREND_ROW Type Parameters

Parameter Description
timepoint The time at which the statistic was recorded
space_usage The space used by data
space_alloc The size of the segment including overhead and unused space
quality The quality of result: "GOOD", "INTERPOLATED", "PROJECTION"

TYPE object_growth_trend_table IS TABLE OF object_growth_trend_row;

SPACE_USAGE Procedure

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

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 98-11 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)
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

Examples

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; 

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 98-12 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, INDEX, or CLUSTER):
  • TABLE

  • TABLE PARTITION

  • TABLE SUBPARTITION

  • INDEX

  • INDEX PARTITION

  • INDEX SUBPARTITION

  • CLUSTER

  • LOB

  • LOB PARTITION

  • LOB SUBPARTITION

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 starting 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.


Examples

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