Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10.1)

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

Go to previous page
Go to next page
View PDF

13 Managing Space for Schema Objects

This chapter offers guidelines for managing space for schema objects. You should familiarize yourself with the concepts in this chapter before attempting to manage specific schema objects as described in later chapters.

This chapter contains the following topics:

Managing Space in Data Blocks

This section describes aspects of managing space in data blocks. Data blocks are the finest level of granularity of the structure in which database data, including schema object segments, is stored on disk. The size of a data block is determined at database creation by initialization parameters, as discussed in "Specifying Database Block Sizes".

The PCTFREE and PCTUSED parameters are physical attributes that can be specified when a schema object is created or altered. These parameters allow you to control the use of the free space within a data block. This free space is available for inserts and updates of rows of data.

The PCTFREE and PCTUSED parameters allow you to:

The INITRANS parameter is another physical attribute that can be specified when schema objects are created or altered. It is used to ensure that a minimum number of concurrent transactions can update the data block.

The following topics are contained in this section:

Specifying the PCTFREE Parameter

The PCTFREE parameter is used to set the percentage of a block to be reserved for possible updates to rows that already are contained in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:


This indicates that 20% of each data block used for the data segment of this table will be kept free and available for possible updates to the existing rows already within each block. Figure 13-1 illustrates PCTFREE.

Notice that before the block reaches PCTFREE, the free space of the data block is filled by both the insertion of new rows and by the growth of the data block header.

Ensure that you understand the nature of table or index data before setting PCTFREE. Updates can cause rows to grow. New values might not be the same size as values they replace. If there are many updates in which data values get larger, PCTFREE should be increased. If updates to rows do not affect the total row width, PCTFREE can be low. Your goal is to find a satisfactory trade-off between densely packed data and good update performance.

The default for PCTFREE is 10 percent. You can use any integer between 0 and 99, inclusive, as long as the sum of PCTFREE and PCTUSED does not exceed 100.

Effects of Specifying a Smaller PCTFREE

A smaller PCTFREE has the following effects:

  • Reserves less room for updates to expand existing table rows

  • Allows inserts to fill the block more completely

  • May save space, because the total data for a table or index is stored in fewer blocks (more rows or entries for each block)

A small PCTFREE might be suitable, for example, for a segment that is rarely changed.

Effects of Specifying a Larger PCTFREE

A larger PCTFREE has the following effects:

  • Reserves more room for future updates to existing table rows

  • May require more blocks for the same amount of inserted data (inserting fewer rows for each block)

  • May improve update performance, because the database does not need to migrate rows as frequently, if ever

A large PCTFREE is suitable, for example, for segments that are frequently updated.

PCTFREE for Nonclustered Tables

If the data in the rows of a nonclustered table is likely to increase in size over time, reserve some space for these updates. Otherwise, updated rows are likely to be migrated. This happens when updates to a row cause the row to no longer fit in the data block because there is not enough free space left. When a row is migrated, a row piece is left in the original data block pointing to the actual row data stored in another block. This decreases I/O performance.

PCTFREE for Clustered Tables

The discussion for nonclustered tables also applies to clustered tables. However, if PCTFREE is reached, new rows from any table contained in the same cluster key go into a new data block that is chained to the existing cluster key.

PCTFREE for Indexes

You can specify PCTFREE only when initially creating an index.

Specifying the PCTUSED Parameter


The PCTUSED parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO. This form of segment space management is discussed in "Specifying Segment Space Management in Locally Managed Tablespaces".

After a data block becomes full as determined by PCTFREE, the database does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is achieved, the database uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter within a CREATE TABLE statement:


In this case, a data block used for the data segment of this table is not considered for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the used space in the block has previously reached PCTFREE). Figure 13-2 illustrates this.

The default value for PCTUSED is 40 percent. After the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED. The percent value is for the block space available for data after overhead is subtracted from total space.

You can specify any integer between 0 and 99 (inclusive) for PCTUSED, as long as the sum of PCTUSED and PCTFREE does not exceed 100.

Effects of Specifying a Smaller PCTUSED

A smaller PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when it has fallen below that percentage of usage. It also increases the unused space in a database.

Effects of Specifying a Larger PCTUSED

A larger PCTUSED improves space efficiency and increases processing cost during INSERT and UPDATE.

Selecting Associated PCTUSED and PCTFREE Values

If you decide not to use the default values for PCTFREE or PCTUSED, keep the following guidelines in mind:

  • The sum of PCTFREE and PCTUSED must be equal to or less than 100.

  • If the sum equals 100, then the database attempts to keep no more than PCTFREE free space, and processing costs are highest.

  • The smaller the difference between 100 and the sum of PCTFREE and PCTUSED (as in PCTUSED of 75, PCTFREE of 20), the more efficient space usage is, at some performance cost.

The following table contains examples that show how and why specific values for PCTFREE and PCTUSED are specified for tables.

Example Scenario Settings Explanation
1 Common activity includes UPDATE statements that increase the size of the rows. PCTFREE=20


PCTFREE is set to 20 to allow enough room for rows that increase in size as a result of updates. PCTUSED is set to 40 so that less processing is done during high update activity, thus improving performance.
2 Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows. PCTFREE=5


PCTFREE is set to 5 because most UPDATE statements do not increase row sizes. PCTUSED is set to 60 so that space freed by DELETE statements is used soon, yet processing is minimized.
3 The table is very large and storage is a primary concern. Most activity includes read-only transactions. PCTFREE=5


PCTFREE is set to 5 because this is a large table and you want to completely fill each block.

Specifying the INITRANS Parameter

INITRANS specifies the number of update transaction entries for which space is initially reserved in the data block header. Space is reserved in the headers of all data blocks in the associated segment.

As multiple transactions concurrently access the rows of the same data block, space is allocated for each update transaction entry in the block. Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of the free space in a block, if available. Once allocated, this space effectively becomes a permanent part of the block header.


In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

The database ignores MAXTRANS when specified by users only for new objects created when the COMPATIBLE initialization parameter is set to 10.0 or greater.

You should consider the following when setting the INITRANS parameter for a schema object:

  • The space you would like to reserve for transaction entries compared to the space you would reserve for database data

  • The number of concurrent transactions that are likely to touch the same data blocks at any given time

For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low, especially if space is at a premium in the database.

Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider preallocating transaction entry space by using a high INITRANS. This eliminates the overhead of having to allocate transaction entry space, as required when the object is in use.

In general, Oracle recommends that you not change the value of INITRANS from its default.

Managing Space in Tablespaces

Oracle Database provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. Two alert thresholds are defined by default: warning and critical. The warning threshold is the limit at which space is beginning to run low. The critical threshold is a serious limit that warrants your immediate attention. The database issues alerts at both thresholds.

When you create a new database, all tablespaces are assigned a default of 85% full for the warning threshold and 97% full for the critical threshold. In a migrated database, the thresholds are both set to NULL. The NULL setting effectively disables the alert mechanism to avoid excessive alerts in a newly migrated database. In either case, you can reset the threshold values, for the entire database or for individual tablespaces, using DBMS_SERVER_ALERT package. You use the procedures SET_THRESHOLD and GET_THRESHOLD to set and get threshold values, respectively.

The ideal setting for the warning threshold is one that issues an alert early enough for you to resolve the problem before it becomes critical. The critical threshold should be one that issues an alert still early enough that you can take immediate action to avoid loss of service.

Threshold-based alerts have the following limitations:

See Also:

Managing Storage Parameters

This section describes the storage parameters that you can specify for schema object segments to tell the database how to store the object in the database. Schema objects include tables, indexes, partitions, clusters, materialized views, and materialized view logs

The following topics are contained in this section:

Identifying the Storage Parameters

Storage parameters determine space allocation for objects when their segments are created in a tablespace. Not all storage parameters can be specified for every type of database object, and not all storage parameters can be specified in both the CREATE and ALTER statements.

Space allocation is less complex in locally managed tablespaces than in dictionary-managed tablespaces. Storage parameters for objects in locally managed tablespaces are supported mainly for backward compatibility, and they are interpreted differently or are ignored.

For locally managed tablespaces, the Oracle Database server manages extents for you. If you specified the UNIFORM clause when the tablespace was created, then you told the database to create all extents of a uniform size that you specified (or a default size) for any objects created in the tablespace. If you specified the AUTOALLOCATE clause, then the database determines the extent sizing policy for the tablespace. So, for example, if you specific the INITIAL clause when you create an object in a locally managed tablespace you are telling the database to preallocate at least that much space. The database then determines the appropriate number of extents needed to allocate that much space.

Table 13-1 contains a brief description of each storage parameter. For a complete description of these parameters, including their default, minimum, and maximum settings, see the Oracle Database SQL Reference.

Table 13-1 Storage Parameters

Parameter Description
INITIAL In a dictionary-managed tablespace, the size, in bytes, of the first extent allocated when a segment is created. This parameter cannot be specified in an ALTER statement.

In a tablespace that is specified as EXTENT MANAGEMENT LOCAL. The database uses the value of INITIAL in conjunction with the extent size for the tablespace to determine the initial amount of space to reserve for the object. For example, in a uniform locally managed tablespace with 5M extents, if you specify an INITIAL value of 1M, then the database must allocate one 5M extent, because that is the uniform size of extents for the tablespace. If the extent size of the tablespace is smaller than the value of INITIAL, then the initial amount of space allocated will in fact be more than one extent.

NEXT In a dictionary-managed tablespace, the size, in bytes, of the next incremental extent to be allocated for a segment. The second extent is equal to the original setting for NEXT. From there forward, NEXT is set to the previous size of NEXT multiplied by (1 + PCTINCREASE/100).

Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages extents.

PCTINCREASE In a dictionary-managed tablespace, the percentage by which each incremental extent grows over the last incremental extent allocated for a segment. If PCTINCREASE is 0, then all incremental extents are the same size. If PCTINCREASE is greater than zero, then each time NEXT is calculated, it grows by PCTINCREASE. PCTINCREASE cannot be negative.

The new NEXT equals 1 + PCTINCREASE/100, multiplied by the size of the last incremental extent (the old NEXT) and rounded up to the next multiple of a block size.

Not meaningful for objects created in a tablespace that is specified as EXTENT MANAGEMENT LOCAL because the database automatically manages the extents.

MINEXTENTS In a dictionary-managed tablespace, the total number of extents to be allocated when the segment is created. This allows for a large allocation of space at creation time, even if contiguous space is not available.

In a tablespace that is specified as EXTENT MANAGEMENT LOCAL, MINEXTENTS is used only to compute the initial amount of space that is allocated. The initial amount of space that is allocated and is equal to INITIAL * MINEXTENTS. Thereafter it is set to 1 for these tablespaces. (as seen in the DBA_SEGMENTS view).

MAXEXTENTS In a dictionary-managed tablespace, the total number of extents, including the first, that can ever be allocated for the segment.

Not meaningful for objects created in locally managed tablespaces because the database automatically manages the extents.

FREELIST GROUPS The number of groups of free lists for the database object you are creating. The database uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group.

This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.

FREELISTS Specifies the number of free lists for each of the free list groups for the schema object. Not valid for tablespaces.

This parameter is ignored for objects created in locally managed tablespaces with segment space management specified as AUTO.

BUFFER POOL Defines a default buffer pool (cache) for a schema object. For information on the use of this parameter, see Oracle Database Performance Tuning Guide.

Setting Default Storage Parameters for Objects Created in a Tablespace

When you create a dictionary-managed tablespace you can specify default storage parameters. These values override the system defaults to become the defaults for objects created in that tablespace only. You specify the default storage values in the DEFAULT STORAGE clause of a CREATE or ALTER TABLESPACE statement. For a tablespace which you specifically create as locally managed, the DEFAULT STORAGE clause is not allowed.

Specifying Storage Parameters at Object Creation

At object creation, you can specify storage parameters for each individual schema object. These parameter settings override any default storage settings. Use the STORAGE clause of the CREATE or ALTER statement for specifying storage parameters for the individual object. The following example illustrates specifying storage parameters when a table is being created in a dictionary-managed tablespace:

        (code  NUMBER(10) PRIMARY KEY,
         lastname  VARCHAR(20),
         firstname VARCHAR(15),
         position  VARCHAR2(20), 
         team VARCHAR2(20))
      PCTFREE 10 
      PCTUSED 40
         (INITIAL 25K
          NEXT 10K
          MAXEXTENTS 10
          MINEXTENTS 3);

For an object created in a locally managed tablespace, only the INITIAL parameter has meaning.

Setting Storage Parameters for Clusters

You set the storage parameters for nonclustered tables using the STORAGE clause of the CREATE TABLE or ALTER TABLE statement.

In contrast, you set the storage parameters for the data segments of a cluster using the STORAGE clause of the CREATE CLUSTER or ALTER CLUSTER statement, rather than the individual CREATE or ALTER statements that put tables into the cluster. Storage parameters specified when creating or altering a clustered table are ignored. The storage parameters set for the cluster override the table storage parameters.

Setting Storage Parameters for Partitioned Tables

With partitioned tables, you can set default storage parameters at the table level. When creating a new partition of the table, the default storage parameters are inherited from the table level (unless you specify them for the individual partition). If no storage parameters are specified at the table level, then they are inherited from the tablespace.

Setting Storage Parameters for Index Segments

Storage parameters for an index segment created for a table index can be set using the STORAGE clause of the CREATE INDEX or ALTER INDEX statement.

Storage parameters of an index segment created for the index used to enforce a primary key or unique key constraint can be set in either of the following ways:

  • In the ENABLE ... USING INDEX clause of the CREATE TABLE or ALTER TABLE statement

  • In the STORAGE clause of the ALTER INDEX statement

Setting Storage Parameters for LOBs, Varrays, and Nested Tables

A table or materialized view can contain LOB, varray, or nested table column types. These entities can be stored in their own segments. LOBs and varrays are stored in LOB segments, while a nested table is stored in a storage table. You can specify a STORAGE clause for these segments that will override storage parameters specified at the table level.

See Also:

Changing Values of Storage Parameters

You can alter default storage parameters for tablespaces and specific storage parameters for individual objects if you so choose. Default storage parameters can be reset for a tablespace; however, changes affect only new objects created in the tablespace or new extents allocated for a segment. As discussed previously, you cannot specify default storage parameters for locally managed tablespaces, so this discussion does not apply.

The INITIAL and MINEXTENTS storage parameters cannot be altered for an existing table, cluster, index. If only NEXT is altered for a segment, the next incremental extent is the size of the new NEXT, and subsequent extents can grow by PCTINCREASE as usual.

If both NEXT and PCTINCREASE are altered for a segment, the next extent is the new value of NEXT, and from that point forward, NEXT is calculated using PCTINCREASE as usual.

Understanding Precedence in Storage Parameters

Starting with default values, the storage parameters in effect for a database object at a given time are determined by the following, listed in order of precedence (where higher numbers take precedence over lower numbers):

  1. Oracle Database default values





Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level. When storage parameters are not explicitly set at the object level, they default to those at the tablespace level. When storage parameters are not set at the tablespace level, Oracle Database system defaults apply. If storage parameters are altered, the new options apply only to the extents not yet allocated.


The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace.

Example of How Storage Parameters Effect Space Allocation

This discussion applies only to objects created in dictionary-managed tablespaces. For objects created in locally managed tablespaces, extents will either be system managed, or will all be of the same size.

Assume the following statement has been executed:

CREATE TABLE test_storage
   ( . . . )
      PCTINCREASE 50);

Also assume that the initialization parameter DB_BLOCK_SIZE is set to 2K. The following table shows how extents are allocated for the test_storage table. Also shown is the value for the incremental extent, as can be seen in the NEXT column of the USER_SEGMENTS or DBA_SEGMENTS data dictionary views:

Table 13-2 Extent Allocations

Extent# Extent Size Value for NEXT
1 50 blocks or 102400 bytes 50 blocks or 102400 bytes
2 50 blocks or 102400 bytes 75 blocks or153600 bytes
3 75 blocks or 153600 bytes 113 blocks or 231424 bytes
4 115 blocks or 235520 bytes 170 blocks or 348160 bytes
5 170 blocks or 348160 bytes No next value, MAXEXTENTS=5

If you change the NEXT or PCTINCREASE storage parameters with an ALTER statement (such as ALTER TABLE), the specified value replaces the current value stored in the data dictionary. For example, the following statement modifies the NEXT storage parameter of the test_storage table before the third extent is allocated for the table:

ALTER TABLE test_storage STORAGE (NEXT 500K);

As a result, the third extent is 500K when allocated, the fourth is (500K*1.5)=750K, and so forth.

Managing Resumable Space Allocation

Oracle Database provides a means for suspending, and later resuming, the execution of large database operations in the event of space allocation failures. This enables you to take corrective action instead of the Oracle Database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation. The statements that are affected are called resumable statements.

This section contains the following topics:

Resumable Space Allocation Overview

This section provides an overview of resumable space allocation. It describes how resumable space allocation works, and specifically defines qualifying statements and error conditions.

How Resumable Space Allocation Works

The following is an overview of how resumable space allocation works. Details are contained in later sections.

  1. A statement executes in a resumable mode only if its session has been enabled for resumable space allocation by one of the following actions:

    • The RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.

    • The ALTER SESSION ENABLE RESUMABLE statement is issued.

  2. A resumable statement is suspended when one of the following conditions occur (these conditions result in corresponding errors being signalled for nonresumable statements):

    • Out of space condition

    • Maximum extents reached condition

    • Space quota exceeded condition.

  3. When the execution of a resumable statement is suspended, there are mechanisms to perform user supplied operations, log errors, and to query the status of the statement execution. When a resumable statement is suspended the following actions are taken:

    • The error is reported in the alert log.

    • The system issues the Resumable Session Suspended alert.

    • If the user registered a trigger on the AFTER SUSPEND system event, the user trigger is executed. A user supplied PL/SQL procedure can access the error message data using the DBMS_RESUMABLE package and the DBA_ or USER_RESUMABLE view.

  4. Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held through a statement suspend and resume.

  5. When the error condition is resolved (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution and the Resumable Session Suspended alert is cleared.

  6. A suspended statement can be forced to throw the exception using the DBMS_RESUMABLE.ABORT() procedure. This procedure can be called by a DBA, or by the user who issued the statement.

  7. A suspension time out interval is associated with resumable statements. A resumable statement that is suspended for the timeout interval (the default is two hours) wakes up and returns the exception to the user.

  8. A resumable statement can be suspended and resumed multiple times during execution.

What Operations are Resumable?


Resumable space allocation is fully supported when using locally managed tablespaces. There are certain limitations when using dictionary-managed tablespaces. See "Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces" for details.

The following operations are resumable:

  • Queries

    SELECT statements that run out of temporary space (for sort areas) are candidates for resumable execution. When using OCI, the calls OCIStmtExecute() and OCIStmtFetch() are candidates.

  • DML

    INSERT, UPDATE, and DELETE statements are candidates. The interface used to execute them does not matter; it can be OCI, SQLJ, PL/SQL, or another interface. Also, INSERT INTO ... SELECT from external tables can be resumable.

  • Import/Export

    As for SQL*Loader, a command line parameter controls whether statements are resumable after recoverable errors.

  • DDL

    The following statements are candidates for resumable execution:










What Errors are Correctable?

There are three classes of correctable errors:

  • Out of space condition

    The operation cannot acquire any more extents for a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition in a tablespace. For example, the following errors fall in this category:

    ORA-1653 unable to extend table ... in tablespace ...
    ORA-1654 unable to extend index ... in tablespace ...
  • Maximum extents reached condition

    The number of extents in a table/index/temporary segment/undo segment/cluster/LOB/table partition/index partition equals the maximum extents defined on the object. For example, the following errors fall in this category:

    ORA-1631 max # extents ... reached in table ...
    ORA-1654 max # extents ... reached in index ...
  • Space quota exceeded condition

    The user has exceeded his assigned space quota in the tablespace. Specifically, this is noted by the following error:

    ORA-1536 space quote exceeded for tablespace string 

Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces

There are certain limitations of resumable space allocation when using dictionary-managed tablespaces:

  1. If a DDL operation such as CREATE TABLE or CREATE INDEX is executed with an explicit MAXEXTENTS setting (or uses the MAXEXTENTS setting from the tablespace DEFAULT STORAGE clause) which causes an out of space error during its execution, the operation will not be suspended. Instead, it will be aborted. This error is treated as not repairable because the properties of an object (for example, MAXEXTENTS) cannot be altered before its creation. However if a DML operation causes an already existing table or index to reach the MAXEXTENTS limit, it will be suspended and can be resumed later. This restriction can be overcome either by setting the MAXEXTENTS clause to UNLIMITED or by using locally managed tablespaces.

  2. If rollback segments are located in dictionary-managed tablespaces, then space allocation for rollback segments is not resumable. However, space allocation for user objects (tables, indexes, and the likes) would still be resumable. To work around the limitation, Oracle recommends using automatic undo management or placing the rollback segments in locally managed tablespaces.

Resumable Space Allocation and Distributed Operations

In a distributed environment, if a user enables or disables resumable space allocation, or if you, as a DBA, alter the RESUMABLE_TIMEOUT initialization parameter, only the local instance is affected. In a distributed transaction, sessions or remote instances are suspended only if RESUMABLE has been enabled in the remote instance.

Parallel Execution and Resumable Space Allocation

In parallel execution, if one of the parallel execution server processes encounters a correctable error, that server process suspends its execution. Other parallel execution server processes will continue executing their respective tasks, until either they encounter an error or are blocked (directly or indirectly) by the suspended server process. When the correctable error is resolved, the suspended process resumes execution and the parallel operation continues execution. If the suspended operation is terminated, the parallel operation aborts, throwing the error to the user.

Different parallel execution server processes may encounter one or more correctable errors. This may result in firing an AFTER SUSPEND trigger multiple times, in parallel. Also, if a parallel execution server process encounters a noncorrectable error while another parallel execution server process is suspended, the suspended statement is immediately aborted.

For parallel execution, every parallel execution coordinator and server process has its own entry in the DBA_ or USER_RESUMABLE view.

Enabling and Disabling Resumable Space Allocation

Resumable space allocation is only possible when statements are executed within a session that has resumable mode enabled. There are two means of enabling and disabling resumable space allocation. You can control it at the system level with the RESUMABLE_TIMEOUT initialization parameter, or users can enable it at the session level using clauses of the ALTER SESSION statement.


Because suspended statements can hold up some system resources, users must be granted the RESUMABLE system privilege before they are allowed to enable resumable space allocation and execute resumable statements.

Setting the RESUMABLE_TIMEOUT Initialization Parameter

You can enable resumable space allocation system wide and specify a timeout interval by setting the RESUMABLE_TIMEOUT initialization parameter. For example, the following setting of the RESUMABLE_TIMEOUT parameter in the initialization parameter file causes all sessions to initially be enabled for resumable space allocation and sets the timeout period to 1 hour:


If this parameter is set to 0, then resumable space allocation is disabled initially for all sessions. This is the default.

You can use the ALTER SYSTEM SET statement to change the value of this parameter at the system level. For example, the following statement will disable resumable space allocation for all sessions:


Within a session, a user can issue the ALTER SESSION SET statement to set the RESUMABLE_TIMEOUT initialization parameter and enable resumable space allocation, change a timeout value, or to disable resumable mode.

Using ALTER SESSION to Enable and Disable Resumable Space Allocation

A user can enable resumable mode for a session, using the following SQL statement:


To disable resumable mode, a user issues the following statement:


The default for a new session is resumable mode disabled, unless the RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.

The user can also specify a timeout interval, and can provide a name used to identify a resumable statement. These are discussed separately in following sections.

Specifying a Timeout Interval

A timeout period, after which a suspended statement will error if no intervention has taken place, can be specified when resumable mode is enabled. The following statement specifies that resumable transactions will time out and error after 3600 seconds:


The value of TIMEOUT remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, it is changed by another means, or the session ends. The default timeout interval when using the ENABLE RESUMABLE TIMEOUT clause to enable resumable mode is 7200 seconds.

See Also:

"Setting the RESUMABLE_TIMEOUT Initialization Parameter " for other methods of changing the timeout interval for resumable space allocation

Naming Resumable Statements

Resumable statements can be identified by name. The following statement assigns a name to resumable statements:


The NAME value remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, or the session ends. The default value for NAME is 'User username(userid), Session sessionid, Instance instanceid'.

The name of the statement is used to identify the resumable statement in the DBA_RESUMABLE and USER_RESUMABLE views.

Using a LOGON Trigger to Set Default Resumable Mode

Another method of setting default resumable mode, other than setting the RESUMABLE_TIMEOUT initialization parameter, is that you can register a database level LOGON trigger to alter a user's session to enable resumable and set a timeout interval.


If there are multiple triggers registered that change default mode and timeout for resumable statements, the result will be unspecified because Oracle Database does not guarantee the order of trigger invocation.

Detecting Suspended Statements

When a resumable statement is suspended, the error is not raised to the client. In order for corrective action to be taken, Oracle Database provides alternative methods for notifying users of the error and for providing information about the circumstances.

Notifying Users: The AFTER SUSPEND System Event and Trigger

When a resumable statement encounter a correctable error, the system internally generates the AFTER SUSPEND system event. Users can register triggers for this event at both the database and schema level. If a user registers a trigger to handle this system event, the trigger is executed after a SQL statement has been suspended.

SQL statements executed within a AFTER SUSPEND trigger are always nonresumable and are always autonomous. Transactions started within the trigger use the SYSTEM rollback segment. These conditions are imposed to overcome deadlocks and reduce the chance of the trigger experiencing the same error condition as the statement.

Users can use the USER_RESUMABLE or DBA_RESUMABLE views, or the DBMS_RESUMABLE.SPACE_ERROR_INFO function, within triggers to get information about the resumable statements.

Triggers can also call the DBMS_RESUMABLE package to terminate suspended statements and modify resumable timeout values. In the following example, the default system timeout is changed by creating a system wide AFTER SUSPEND trigger that calls DBMS_RESUMABLE to set the timeout to 3 hours:

CREATE OR REPLACE TRIGGER resumable_default_timeout

See Also:

Oracle Database Application Developer's Guide - Fundamentals for information about system events, triggers, and attribute functions

Using Views to Obtain Information About Suspended Statements

The following views can be queried to obtain information about the status of resumable statements:

View Description


These views contain rows for all currently executing or suspended resumable statements. They can be used by a DBA, AFTER SUSPEND trigger, or another session to monitor the progress of, or obtain specific information about, resumable statements.
V$SESSION_WAIT When a statement is suspended the session invoking the statement is put into a wait state. A row is inserted into this view for the session with the EVENT column containing "statement suspended, wait error to be cleared".

See Also:

Oracle Database Reference for specific information about the columns contained in these views

Using the DBMS_RESUMABLE Package

The DBMS_RESUMABLE package helps control resumable space allocation. The following procedures can be invoked:

Procedure Description
ABORT(sessionID) This procedure aborts a suspended resumable statement. The parameter sessionID is the session ID in which the statement is executing. For parallel DML/DDL, sessionID is any session ID which participates in the parallel DML/DDL.

Oracle Database guarantees that the ABORT operation always succeeds. It may be called either inside or outside of the AFTER SUSPEND trigger.

The caller of ABORT must be the owner of the session with sessionID, have ALTER SYSTEM privilege, or have DBA privileges.

GET_SESSION_TIMEOUT(sessionID) This function returns the current timeout value of resumable space allocation for the session with sessionID. This returned timeout is in seconds. If the session does not exist, this function returns -1.
SET_SESSION_TIMEOUT(sessionID, timeout) This procedure sets the timeout interval of resumable space allocation for the session with sessionID. The parameter timeout is in seconds. The new timeout setting will applies to the session immediately. If the session does not exist, no action is taken.
GET_TIMEOUT() This function returns the current timeout value of resumable space allocation for the current session. The returned value is in seconds.
SET_TIMEOUT(timeout) This procedure sets a timeout value for resumable space allocation for the current session. The parameter timeout is in seconds. The new timeout setting applies to the session immediately.

Operation-Suspended Alert

When a resumable session is suspended, an operation-suspended alert is issued on the object that needs allocation of resource for the operation to complete. Once the resource is allocated and the operation completes, the operation-suspended alert is cleared. Please refer to "Managing Space in Tablespaces " for more information on system-generated alerts.

Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger

In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:

  • If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.

  • If any other recoverable error has occurred, the timeout interval is reset to 8 hours.

Here are the statements for this example:

   /* declare transaction in this trigger is autonomous */
   /* this is not required because transactions within a trigger
      are always autonomous */
   cur_sid           NUMBER;
   cur_inst          NUMBER;
   errno             NUMBER;
   err_type          VARCHAR2;
   object_owner      VARCHAR2;
   object_type       VARCHAR2;
   table_space_name  VARCHAR2;
   object_name       VARCHAR2;
   sub_object_name   VARCHAR2;
   error_txt         VARCHAR2;
   msg_body          VARCHAR2;
   ret_value         BOOLEAN;
   mail_conn         UTL_SMTP.CONNECTION;
   -- Get session ID

   -- Get instance number
   cur_inst := userenv('instance');

   -- Get space error information
   ret_value := 
        table_space_name,object_name, sub_object_name);
   -- If the error is related to undo segments, log error, send email
   -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
   -- sys.rbs_error is a table which is to be
   -- created by a DBA manually and defined as
   -- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),
   -- suspend_time DATE)

       /* LOG ERROR */
       INSERT INTO sys.rbs_error (
           WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst
           WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;

        -- Send email to receipient via UTL_SMTP package
        msg_body:='Subject: Space Error Occurred

                   Space limit reached for undo segment ' || object_name || 
                   on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||
                   '. Error message was ' || error_txt;

        mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
        UTL_SMTP.HELO(mail_conn, 'localhost');
        UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
        UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
        UTL_SMTP.DATA(mail_conn, msg_body);

        -- Abort the statement
        -- Set timeout to 8 hours
    END IF;

    /* commit autonomous transaction */

Reclaiming Unused Space

Over time, it is common for segment space to become fragmented or for a segment to acquire a lot of free space as the result of update and delete operations. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.

The Segment Advisor can help you determine which objects have space available for reclamation. If the Segment Advisor indicates that an object does have space available for reclamation, you have two ways to release the space so that it can be used by other segments: you can compact and shrink database segments or you can deallocate unused space at the end of a database segment.

The Segment Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces. Please refer to "Automatic Workload Repository" for more information.

Segment Advisor

Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object. The Segment Advisor can generate advice at three levels:

  • At the object level, advice is generated for the entire object, such as a table. If the object is partitioned, then the advice is generated on all the partitions of the object. However, advice does not cascade to dependent objects such as indexes, LOB segments, and so forth.

  • At the segment level, the advice is generated for a single segment, such as unpartitioned table, a partition or subpartition of a partitioned table, or an index or LOB column.

  • At the tablespace level, advice is generated for every segment in the tablespace.

The best way to invoke the Segment Advisor is through Enterprise Manager. Please refer to Oracle Database 2 Day DBA for more information on how to use Enterprise Manager to invoke the Segment Advisor. In addition, you activate the Segment Advisor using procedures of the DBMS_ADVISOR package. Please refer to PL/SQL Packages and Types Reference for details on these parameters. The following procedures are relevant for the Segment Advisor:


Use this procedure to create the Segment Advisor Task. Specify 'Segment Advisor' as the value of the ADVISOR_NAME parameter.


Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type. Table 13-3 lists the parameter values for each type of object.


Input Parameter
TABLESPACE tablespace_name NULL NULL Unused. Specify NULL.
TABLE schema_name table_name NULL Unused. Specify NULL.
INDEX schema_name index_name NULL Unused. Specify NULL.
TABLE PARTITION schema_name table_name table_partition_name Unused. Specify NULL.
INDEX PARTITION schema_name index_name index_partition_name Unused. Specify NULL.
TABLE SUBPARTITION schema_name table_name table_subpartition_name Unused. Specify NULL.
INDEX SUBPARTITION schema_name index_name index_subpartition_name Unused. Specify NULL.


Use this procedure to describe the segment advice you need. Table 13-4 shows the relevant input parameters of this procedure. Parameters not listed here are not used by the Segment Advisor.


Input Parameter Description Possible Values Default Value
MODE The data to use for analyzing the segment. LIMITED: Analysis is restricted to statistics available in the Automatic Workload Repository.

COMPREHENSIVE: Analysis is based on both sampling and Automatic Workload Repository statistics.

TIME_LIMIT The time limit for which the advisor should run, specified in seconds. Any number of seconds UNLIMITED
RECOMMEND_ALL Whether the advisor should generate a recommendation for all segments. TRUE: Findings are generated on all segments specified.

FALSE: Findings are generated only for those objects that are eligible for shrink segment.


The example that follows shows how to use the DBMS_ADVISOR procedures to activate the Segment Advisor for the sample table hr.employees. The user executing these procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege:

variable id number;
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  descr:='Segment Advisor Example';
  dbms_advisor.create_task('Segment Advisor', :id, name, descr, NULL);
     (name, 'TABLE', 'HR', 'EMPLOYEES', NULL, NULL, obj_id);
 dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');

The Segment Advisor creates several types of results:


If you have specified TRUE for RECOMMEND_ALL in the SET_TASK_PARAMETER procedure, then the advisor generates a finding for each segment that qualifies for analysis. You can retrieve the findings by querying the DBA_ADVISOR_FINDINGS data dictionary view.


If segment shrink would result in benefit, then the advisor generates a recommendation for the segment. You can retrieve the recommendations by querying the DBA_ADVISOR_RECOMMENDATIONS dictionary view.


Every advisory recommendation is associated with a suggested action to perform segment shrink. You can retrieve the action by querying the DBA_ADVISOR_ACTIONS data dictionary review. This view provides the SQL you need to perform segment shrink.


All findings, recommendations, and actions are associated with an object. If the input to the advisor results in analysis of more than one segment, as with a tablespace or partitioned table, then one entry is created for each segment in the DBA_ADVISOR_OBJECTS dictionary view. You can correlate the object in this view with the object in the findings, recommendations, and actions views.

Please refer to Oracle Database Reference for a description of these views.

Table 13-5 shows which dictionary columns contain output from the Segment Advisor and summarizes the possible advisor outcomes.

Table 13-5 Segment Advisor Outcomes: Summary

Insufficient information to make a recommendation None None None
Object has less than 1% free space Displays the amount of allocated, used, and reclaimable space. None None
Object has free space but its properties are not compatible with segment shrink. Displays the amount of allocated, used, and reclaimable space. None None
Free space in the object is less than the size of the last extent. Displays the amount of allocated, used, and reclaimable space. None None
Shrink operation is recommended and the estimated savings in bytes is displayed. Displays the amount of allocated, used, and reclaimable space. Shrink operation is recommended and the estimated savings in bytes is displayed. ALTER object SHRINK SPACE;

Shrinking Database Segments

Oracle Database lets you shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause. Shrink operations can be performed only on segments in tablespaces with automatic segment-space management. As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause, described below. Segment shrink is available through the Enterprise Manager interface, which guides you through the required steps. The remainder of this section discusses manually implementing segment shrink.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space. Two optional clauses let you control how the shrink operation proceeds:

  • The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the reallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

  • The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package.

Segment shrink is an online, in-place operation. Unlike other space reorganization methods, segment shrink does not require extra disk space to be allocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated.

Additional benefits of shrink operations are these:

  • Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.

  • The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object. Segment shrink is not supported for LOB segments or for tables with function-based indexes. Please refer to Oracle Database SQL Reference for the syntax and additional information on shrinking a database object, including restrictions.

Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment-space management, use the SPACE_USAGE procedure for more accurate information on unused space.

See Also:

PL/SQL Packages and Types Reference contains the description of the DBMS_SPACE package

The following statements deallocate unused space in a segment (table, index or cluster):


The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify the deallocated space is freed by examining the DBA_FREE_SPACE view.

See Also:

Understanding Space Usage of Datatypes

When creating tables and other data structures, you need to know how much space they will require. Each datatype has different space requirements. The PL/SQL User's Guide and Reference and Oracle Database SQL Reference contain extensive descriptions of datatypes and their space requirements.

Displaying Information About Space Usage for Schema Objects

Oracle Database provides data dictionary views and PL/SQL packages that allow you to display information about the space usage of schema objects. Views and packages that are unique to a particular schema object are described in the chapter of this book associated with that object. This section describes views and packages that are generic in nature and apply to multiple schema objects.

Using PL/SQL Packages to Display Information About Schema Object Space Usage

These Oracle-supplied PL/SQL packages provide information about schema objects:

Package and Procedure/Function Description
DBMS_SPACE.UNUSED_SPACE Returns information about unused space in an object (table, index, or cluster).
DBMS_SPACE.FREE_BLOCKS Returns information about free data blocks in an object (table, index, or cluster) whose segment free space is managed by free lists (segment space management is MANUAL).
DBMS_SPACE.SPACE_USAGE Returns information about free data blocks in an object (table, index, or cluster) whose segment space management is AUTO.

See Also:

PL/SQL Packages and Types Reference for a description of PL/SQL packages


The following SQL*Plus example uses the DBMS_SPACE package to obtain unused space information.

SQL> VARIABLE unused_blocks NUMBER
SQL> VARIABLE lastusedblock NUMBER
SQL> exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, -
>    :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
>    :last_extb, :lastusedblock);

PL/SQL procedure successfully completed.






Using Views to Display Information About Space Usage in Schema Objects

These views display information about space usage in schema objects:

View Description


DBA view describes storage allocated for all database segments. User view describes storage allocated for segments for the current user.


DBA view describes extents comprising all segments in the database. User view describes extents comprising segments for the current user.


DBA view lists free extents in all tablespaces. User view shows free space information for tablespaces for which the user has quota.

The following sections contain examples of using some of these views.

See Also:

Oracle Database Reference for a complete description of data dictionary views

Example 1: Displaying Segment Information

The following query returns the name and size of each index segment in schema hr:


The query output is:

------------------------- --------------- -------- ------ -------
COUNTRY_C_ID_PK           EXAMPLE            65536     32       1
DEPT_ID_PK                EXAMPLE            65536     32       1
DEPT_LOCATION_IX          EXAMPLE            65536     32       1
EMP_DEPARTMENT_IX         EXAMPLE            65536     32       1
EMP_EMAIL_UK              EXAMPLE            65536     32       1
EMP_EMP_ID_PK             EXAMPLE            65536     32       1
EMP_JOB_IX                EXAMPLE            65536     32       1
EMP_MANAGER_IX            EXAMPLE            65536     32       1
EMP_NAME_IX               EXAMPLE            65536     32       1
JHIST_DEPARTMENT_IX       EXAMPLE            65536     32       1
JHIST_EMPLOYEE_IX         EXAMPLE            65536     32       1
JHIST_EMP_ID_ST_DATE_PK   EXAMPLE            65536     32       1
JHIST_JOB_IX              EXAMPLE            65536     32       1
JOB_ID_PK                 EXAMPLE            65536     32       1
LOC_CITY_IX               EXAMPLE            65536     32       1
LOC_COUNTRY_IX            EXAMPLE            65536     32       1
LOC_ID_PK                 EXAMPLE            65536     32       1
LOC_STATE_PROVINCE_IX     EXAMPLE            65536     32       1
REG_ID_PK                 EXAMPLE            65536     32       1

19 rows selected.

Example 2: Displaying Extent Information

Information about the currently allocated extents in a database is stored in the DBA_EXTENTS data dictionary view. For example, the following query identifies the extents allocated to each index segment in the hr schema and the size of each of those extents:


The query output is:

------------------------- ------------ --------------- --------- -------- ------
COUNTRY_C_ID_PK           INDEX        EXAMPLE                 0    65536     32
DEPT_ID_PK                INDEX        EXAMPLE                 0    65536     32
DEPT_LOCATION_IX          INDEX        EXAMPLE                 0    65536     32
EMP_DEPARTMENT_IX         INDEX        EXAMPLE                 0    65536     32
EMP_EMAIL_UK              INDEX        EXAMPLE                 0    65536     32
EMP_EMP_ID_PK             INDEX        EXAMPLE                 0    65536     32
EMP_JOB_IX                INDEX        EXAMPLE                 0    65536     32
EMP_MANAGER_IX            INDEX        EXAMPLE                 0    65536     32
EMP_NAME_IX               INDEX        EXAMPLE                 0    65536     32
JHIST_DEPARTMENT_IX       INDEX        EXAMPLE                 0    65536     32
JHIST_EMPLOYEE_IX         INDEX        EXAMPLE                 0    65536     32
JHIST_EMP_ID_ST_DATE_PK   INDEX        EXAMPLE                 0    65536     32
JHIST_JOB_IX              INDEX        EXAMPLE                 0    65536     32
JOB_ID_PK                 INDEX        EXAMPLE                 0    65536     32
LOC_CITY_IX               INDEX        EXAMPLE                 0    65536     32
LOC_COUNTRY_IX            INDEX        EXAMPLE                 0    65536     32
LOC_ID_PK                 INDEX        EXAMPLE                 0    65536     32
LOC_STATE_PROVINCE_IX     INDEX        EXAMPLE                 0    65536     32
REG_ID_PK                 INDEX        EXAMPLE                 0    65536     32

19 rows selected.

For the hr schema, no segment has more than one extent allocated to it.

Example 3: Displaying the Free Space (Extents) in a Tablespace

Information about the free extents (extents not allocated to any segment) in a database is stored in the DBA_FREE_SPACE data dictionary view. For example, the following query reveals the amount of free space available as free extents in the SMUNDO tablespace:


The query output is:

--------------- -------- -------- ------
SMUNDO                 3    65536     32
SMUNDO                 3    65536     32
SMUNDO                 3    65536     32
SMUNDO                 3    65536     32
SMUNDO                 3    65536     32
SMUNDO                 3    65536     32
SMUNDO                 3   131072     64
SMUNDO                 3   131072     64
SMUNDO                 3    65536     32
SMUNDO                 3  3407872   1664

10 rows selected.

Example 4: Displaying Segments that Cannot Allocate Additional Extents

It is possible that a segment cannot be allocated to an extent for any of the following reasons:

  • The tablespace containing the segment does not have enough room for the next extent.

  • The segment has the maximum number of extents.

  • The segment has the maximum number of extents allowed by the data block size, which is operating system specific.

The following query returns the names, owners, and tablespaces of all segments that satisfy any of these criteria:

    OR a.EXTENTS = 'data_block_size' ;


When you use this query, replace data_block_size with the data block size for your system.

Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:

  • If the tablespace is full, add a datafile to the tablespace or extend the existing datafile.

  • If the segment has too many extents, and you cannot increase MAXEXTENTS for the segment, perform the following steps.

    1. Export the data in the segment

    2. Drop and re-create the segment, giving it a larger INITIAL storage parameter setting so that it does not need to allocate so many extents. Alternatively, you can adjust the PCTINCREASE and NEXT storage parameters to allow for more space in the segment.

    3. Import the data back into the segment.

Capacity Planning for Database Objects

Oracle Database provides three procedures in the DBMS_SPACE package that let you predict the size of new objects and monitor the size of existing database objects. This section discusses those procedures and contains the following sections:

Estimating the Space Use of a Table

The size of a database table can vary greatly depending on tablespace storage attributes, tablespace block size, and many other factors. The CREATE_TABLE_COST procedure of the DBMS_SPACE package lets you estimate the space use cost of creating a table. Please refer to [ARPLS] for details on the parameters of this procedure.

The procedure has two variants. The first variant uses average row size to estimate size. The second variant uses column information to estimate table size. Both variants require as input the following values:

  • TABLESPACE_NAME: The tablespace in which the object will be created. The default is the SYSTEM tablespace.

  • ROW_COUNT: The anticipated number of rows in the table.

  • PCT_FREE: The percentage of free space you want to reserve in each block for future expansion of existing rows due to updates.

In addition, the first variant also requires as input a value for AVG_ROW_SIZE, which is the anticipated average row size in bytes.

The second variant also requires for each anticipated column values for COLINFOS, which is an object type comprising the attributes COL_TYPE (the datatype of the column) and COL_SIZE (the number of characters or bytes in the column).

The procedure returns two values:

  • USED_BYTES: The actual bytes used by the data, including overhead for block metadata, PCT_FREE space, and so forth.

  • ALLOC_BYTES: The amount of space anticipated to be allocated for the object taking into account the tablespace extent characteristics.

Estimating the Space Use of an Index

The CREATE_INDEX_COST procedure of the DBMS_SPACE package lets you estimate the space use cost of creating an index on an existing table.

The procedure requires as input the following values:

  • DDL: The CREATE INDEX statement that would create the index. The table specified in this DDL statement must be an existing table.

  • [Optional] PLAN_TABLE: The name of the plan table to use. The default is NULL.

The results returned by this procedure depend on statistics gathered on the segment. Therefore, be sure to obtain statistics shortly before executing this procedure. In the absence of recent statistics, the procedure does not issue an error, but it may return inappropriate results. The procedure returns the following values:

  • USED_BYTES: The number of bytes representing the actual index data.

  • ALLOC_BYTES: The amount of space allocated for the index in the tablespace.

Obtaining Object Growth Trends

The OBJECT_GROWTH_TREND procedure of the DBMS_SPACE package produces a table of one or more rows, where each row describes the space use of the object at a specific time. The procedure retrieves the space use totals from the Automatic Workload Repository or computes current space use and combines it with historic space use changes retrieved from Automatic Workload Repository. Please refer to [ARPLS] for detailed information on the parameters of this procedure.

The procedure requires as input the following values:

  • OBJECT_OWNER: The owner of the object.

  • OBJECT_NAME: The name of the object.

  • PARTITION_NAME: The name of the table or index partition, is relevant. Specify NULL otherwise.

  • OBJECT_TYPE: The type of the object.

  • START_TIME: A TIMESTAMP value indicating the beginning of the growth trend analysis.

  • END_TIME: A TIMESTAMP value indicating the end of the growth trend analysis. The default is "NOW".

  • INTERVAL: The length in minutes of the reporting interval during which the procedure should retrieve space use information.

  • SKIP_INTERPOLATED: Determines whether the procedure should omit values based on recorded statistics before and after the INTERVAL ('YES') or not ('NO'). This setting is useful when the result table will be displayed as a table rather than a chart, because you can see more clearly how the actual recording interval relates to the requested reporting interval.

The procedure returns a table, each of row of which provides space use information on the object for one interval. If the return table is very large, the results are pipelined so that another application can consume the information as it is being produced. The output table has the following columns:

  • TIMEPOINT: A TIMESTAMP value indicating the time of the reporting interval.

    Records are not produced for values of TIME that precede the oldest recorded statistics for the object.

  • SPACE_USAGE: The number of bytes actually being used by the object data.

  • SPACE_ALLOC: The number of bytes allocated to the object in the tablespace at that time.

  • QUALITY: A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.

    The values of the QUALITY column are:

    • GOOD: The value whenever the value of TIME is based on recorded statistics with a recorded timestamp within 10% of the INTERVAL specified in the input parameters.

    • INTERPOLATED: The value did not meet the criteria for GOOD, but was based on recorded statistics before and after the value of TIME. Current in-memory statistics can be collected across all instances in a cluster and treated as the "recorded" value for the present time.

    • PROJECTION: The value of TIME is in the future as of the time the table was produced. In a Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected.

    The output returned by this procedure is an aggregation of values recorded across all instances in a RAC environment. Each value can be computed from a combination of GOOD and INTERPOLATED values. The aggregate value returned is marked GOOD if at least 80% of that value was derived from GOOD instance values.