Managing Storage Parameters - Oracle 10g

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
  • Setting Default Storage Parameters for Objects Created in a Tablespace
  • Specifying Storage Parameters at Object Creation
  • Setting Storage Parameters for Clusters
  • Setting Storage Parameters for Partitioned Tables
  • Setting Storage Parameters for Index Segments
  • Setting Storage Parameters for LOBs, Varrays, and Nested Tables
  • Changing Values of Storage Parameters
  • Understanding Precedence in Storage Parameters
  • Example of How Storage Parameters Effect Space Allocation

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

For a complete description of these parameters, including their default, minimum, and maximum settings, see the Oracle Database SQL Reference.

Identifying the Storage ParametersIdentifying the Storage ParametersIdentifying the Storage Parameters

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:


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.

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
  2. DEFAULT STORAGE clause of CREATE TABLESPACE statement
  3. DEFAULT STORAGE clause of ALTER TABLESPACE statement
  4. STORAGE clause of CREATE [TABLE | CLUSTER | MATERIALIZED VIEW |MATERIALIZED VIEW LOG | INDEX] statement
  5. STORAGE clause of ALTER [TABLE | CLUSTER | MATERIALIZED VIEW |MATERIALIZED VIEW LOG | INDEX] statement

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.

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:


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:

Extent Allocations

Extent Allocations

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics