Skip Headers

Oracle9i Database Administrator's Guide
Release 2 (9.2)

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

Master Index


Go to previous page Go to next page

Managing Space for Schema Objects

This chapter offers guidelines for managing space for schema objects. It contains the following topics:

You should familiarize yourself with the concepts in this chapter before attempting to manage specific schema objects as described in later chapters.

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 is stored on disk. The size of a data block is specified (or defaulted) at database creation.

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 and MAXTRANS parameters are also physical attributes that can be specified when schema objects are created or altered. These parameters control the number of concurrent update transactions allocated for data blocks of a schema object, which in turn affects space usage in data block headers and can have an impact upon data block free space.

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 this table's data segment will be kept free and available for possible updates to the existing rows already within each block. Figure 14-1 illustrates PCTFREE.

Figure 14-1 PCTFREE

Text description of admin018.gif follows
Text description of the illustration admin018.gif

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 a 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:

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:

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 chained among blocks.

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, Oracle 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, Oracle 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 this table's data segment 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 block's used space has previously reached PCTFREE). Figure 14-2 illustrates this.

Figure 14-2 PCTUSED

Text description of admin019.gif follows
Text description of the illustration admin019.gif

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 has the following effects:

Effects of Specifying a Larger PCTUSED

A larger PCTUSED has the following effects:

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 following table contains examples that show how and why specific values for PCTFREE and PCTUSED are specified for tables.

Example Scenario Settings Explanation


Common activity includes UPDATE statements that increase the size of the rows.



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.


Most activity includes INSERT and DELETE statements, and UPDATE statements that do not increase the size of affected rows.



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.


The table is very large and storage is a primary concern. Most activity includes read-only transactions.



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

Specifying the Transaction Entry Parameters: INITRANS and MAXTRANS

INITRANS specifies the number of DML 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 DML transaction's 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. The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS.

The INITRANS and MAXTRANS parameters for the data blocks allocated to a specific schema object should be set individually for each schema object based on the following criteria:

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. Also, allow a higher MAXTRANS so that no user has to wait to access necessary data blocks.

Setting Storage Parameters

This section describes the storage parameters that you can set for various data structures. These storage parameters apply to the following types of structures and schema objects:

The following topics are discussed:

Identifying the Storage Parameters

Storage parameters determine space allocation for objects when they are created in a dictionary-managed tablespace. Locally managed tablespaces provide a simpler means of space allocation, and most storage parameters have no meaning in their context.

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.

Furthermore, for objects created in dictionary-managed tablespaces, 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:

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

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.

The following table contains a brief description of each storage parameter. For a complete description of these parameters, including their default, minimum, and maximum settings, see the Oracle9i SQL Reference.

Parameter Description


The size, in bytes, of the first extent allocated when a segment is created. This parameter cannot be specified in an ALTER statement.


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


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.


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.


The total number of extents, including the first, that can ever be allocated for the segment.


The number of groups of free lists for the database object you are creating. Oracle uses the instance number of Oracle Real Application Cluster instances to map each instance to one free list group. For information on the use of this parameter, see Oracle9i Real Application Clusters Administration.

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


Specifies the number of free lists for each of the free list groups for the schema object. Not valid for tablespaces.The use of this parameter is discussed in Oracle9i Database Performance Tuning Guide and Reference.

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


Relevant only to rollback segments. See Chapter 13, "Managing Undo Space" for information on the use of this parameter.


Defines a default buffer pool (cache) for a schema object. Not valid for tablespaces or rollback segments. For information on the use of this parameter, see Oracle9i Database Performance Tuning Guide and Reference.

Setting Default Storage Parameters for Segments in a Tablespace

You can set default storage parameters for each tablespace of a database. Any storage parameter that you do not explicitly set when creating or subsequently altering a segment in a tablespace automatically is set to the corresponding default storage parameter for the tablespace in which the segment resides.

When specifying MINEXTENTS at the tablespace level, any extent allocated in the tablespace is rounded to a multiple of the number of minimum extents.

Setting Storage Parameters for Data Segments

You set the storage parameters for the data segment of a nonclustered table, materialized view, or materialized view log using the STORAGE clause of the CREATE or ALTER statement for tables, materialized views, or materialized view logs.

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 and materialized views into the cluster. Storage parameters specified when creating or altering a clustered table or materialized view are ignored. The storage parameters set for the cluster override the table's storage parameters.

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:

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:

All of the above books contain more information about creating tables containing LOBs, varrays, and nested tables.

Changing Values for Storage Parameters

You can alter default storage parameters for tablespaces and specific storage parameters for individual segments 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.

The INITIAL and MINEXTENTS storage parameters cannot be altered for an existing table, cluster, index, or rollback segment. 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

The storage parameters in effect at a given time are determined by the following types of SQL statements, listed in order of precedence (where higher numbers take precedence over lower numbers):

  3. ALTER TABLESPACE statement
  4. CREATE TABLESPACE statement
  5. Oracle 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 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

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 14-1 Extent Allocations
Extent# Extent Size Value for NEXT


50 blocks or 102400 bytes

50 blocks or 102400 bytes


50 blocks or 102400 bytes

75 blocks or153600 bytes


75 blocks or 153600 bytes

113 blocks or 231424 bytes


115 blocks or 235520 bytes

170 blocks or 348160 bytes


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 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 statements work, and specifically defines qualifying statements and error conditions.

How Resumable Statements Work

The following is an overview of how resumable statements work. Details are contained in later sections.

  1. A statement executes in a resumable mode only when the client explicitly enables resumable semantics for the session using the ALTER SESSION statement.
  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. On suspending a resumable statement's execution, 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.
    • 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 DBA/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 disappears (for example, as a result of user intervention or perhaps sort space released by other queries), the suspended statement automatically resumes execution.
  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:

What Errors are Correctable?

There are three classes of correctable errors:

Resumable Space Allocation Limitations for Dictionary-Managed Tablespaces

There are certain limitations of resumable space allocation when using dictionary-managed tablespaces. These limitations are listed below:

  1. If a DDL operation such as CREATE TABLE or CREATE INDEX is executed with an explicit MAXEXTENTS setting 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 workaround the limitation, we recommend using automatic undo management or placing the rollback segments in locally managed tablespaces.

Resumable Statements and Distributed Operations

Remote operations are not supported in resumable mode.

Parallel Execution and Resumable Statements

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 DBA/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.

To enable resumable mode for a session, use the following SQL statement:


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

To disable resumable mode, issue the following statement:


The default for a new session is resumable mode disabled.

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

See Also:

"Setting Default Resumable Mode"

Specifying a Timeout Interval

When you enable resumable mode for a session, you can also specify a timeout interval, after which a suspended statement will error if no intervention has taken place. 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 is 7200 seconds.

See Also:

"Changing the Timeout Interval" for other methods of changing the timeout interval for resumable statements

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:


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

Setting Default Resumable Mode

To set default resumable mode, a DBA 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 does not guarantee the order of trigger invocation.

Changing the Timeout Interval

In addition to the ALTER SESSION ENABLE RESUMABLE statement, there are other methods for setting or changing the timeout interval.

The DBMS_RESUMABLE package contains procedures for setting the timeout period for a specific session or for the current session. A DBA can change the default system timeout by creating a system wide AFTER SUSPEND trigger that calls DBMS_RESUMABLE to set it. For example, the following code sample sets a system wide default timeout to one hour:

CREATE OR REPLACE TRIGGER resumable_default_timeout

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 provides alternative methods for notifying users of the error and for providing information about the circumstances.

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 abort suspended statements and modify resumable timeout values.

See Also:

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

Views Containing Information About Resumable 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.


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:

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


The DBMS_RESUMABLE package helps control resumable statements. The following procedures are available:

Procedure Description


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


This function returns the current timeout value of resumable statements 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 statements 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.


This function returns the current timeout value of resumable statements for the current session. The returned value is in seconds.


This procedure sets a timeout value for resumable statements for the current session. The parameter timeout is in seconds. The new timeout setting applies to the session immediately.

See Also:

Oracle9i Supplied PL/SQL Packages and Types Reference

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:

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 rollback segments, log error, send email
   -- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
   -- sys.rbs_error is created by 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 rollback 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 */

Deallocating Space

It is not uncommon to allocate space to a segment, only to find out later that it is not being used. For example, you can set PCTINCREASE to a high value, which could create a large extent that is only partially used. Or, you could explicitly overallocate space by issuing the ALTER TABLE ... ALLOCATE EXTENT statement. If you find that you have unused or overallocated space, you can release it so that the unused space can be used by other segments.

This section describes aspects of deallocating unused space.

Viewing the High Water Mark

Prior to deallocation, you can use the DBMS_SPACE package, which contains a procedure (UNUSED_SPACE) that returns information about the position of the high water mark and the amount of unused space in a segment.

Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the high water mark (even if there is no data in the space you want to deallocate). However, if the segment is completely empty, you can release space using the TRUNCATE ... DROP STORAGE statement.

For segments in locally managed tablespaces with segment space management specified as AUTO, the following output parameters still determine the high water mark, put their meaning is somewhat altered:

Specifically, it is possible for some blocks below the high water mark to be unformatted. Neither the UNUSED_SPACE nor the FREE_SPACE procedure of DBMS_SPACE accurately accounts for unused space when segment space management is specified as AUTO. Use the SPACE_USAGE procedure instead.

See Also:

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

Issuing Space Deallocation Statements

The following statements deallocate unused space in a segment (table, index or cluster). The KEEP clause is optional.


When you explicitly identify an amount of unused space to KEEP, this space is retained while the remaining unused space is deallocated. If the remaining number of extents becomes smaller than MINEXTENTS, the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller, the INITIAL value changes to reflect the new size of the initial extent.

If you do not specify the KEEP clause, all unused space (everything above the high water mark) is deallocated, as long as the size of the initial extent and MINEXTENTS are preserved. Thus, even if the high water mark occurs within the MINEXTENTS boundary, MINEXTENTS remains and the initial extent size is not reduced.

You can verify the deallocated space is freed by examining the DBA_FREE_SPACE view.

See Also:

Examples of Deallocating Space

This section provides some space deallocation examples.

Deallocating Space Example 1:

A table consists of three extents. The first extent is 10K, the second is 20K, and the third is 30K. The high water mark is in the middle of the second extent, and there is 40K of unused space. Figure 14-3 illustrates the effect of issuing the following statement:


All unused space is deallocated, leaving table dquon with two remaining extents. The third extent disappears, and the second extent size is 10K.

Figure 14-3 Deallocating All Unused Space

Text description of admin015.gif follows
Text description of the illustration admin015.gif

But, if you had issued the following statement specifying the KEEP keyword, then 10K above the high water mark would be kept, and the rest of the unused space would be deallocated from dquon.


In effect, the third extent is deallocated and the second extent remains intact.

Figure 14-4 illustrates this situation.

Figure 14-4 Deallocating Unused Space, KEEP 10K

Text description of admin016.gif follows
Text description of the illustration admin016.gif

Further, if you deallocate all unused space from dquon and keep 20K, as specified in the following statement, the third extent is cut to 10K, and the size of the second extent remains the same.

Deallocating Space Example 2:

Consider the situation illustrated by Figure 14-3. Extent 3 is completely deallocated, and the second extent is left with 10K. Further, the size of the next allocated extent defaults to the size of the last completely deallocated extent, which in this case, is 30K. If this is not what you want, you can explicitly set the size of the next extent using the ALTER TABLE statement, specifying a new value for NEXT in the storage clause.

The following statement sets the next extent size for table dquon to 20K:

Deallocating Space Example 3:

To preserve the MINEXTENTS number of extents, DEALLOCATE can retain extents that were originally allocated to a segment. This capacity is influenced by the KEEP parameter and was explained earlier.

If table dquon has a MINEXTENTS value of 2, the statements illustrated in Figure 14-3 and Figure 14-4 still yield the same results as shown, and further, the initial value of MINEXTENTS is preserved.

However, if the MINEXTENTS value is 3, then the statement illustrated in Figure 14-4 produces the same result as shown (the third extent is removed), but the value of MINEXTENTS is changed to 2. However, the statement illustrated in Figure 14-3 does not produce the same result. In this case, the statement has no effect.

Understanding Space Use 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 Oracle9i SQL Reference contain extensive descriptions of datatypes and their space requirements.

Go to previous page Go to next page
Copyright © 2001, 2002 Oracle Corporation.

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

Master Index