Setting Storage Parameters

• Storage Parameters You Can Specify

• Setting INITRANS and MAXTRANS

• Setting Default Storage Parameters for Segments in a Tablespace

• Setting Storage Parameters for Data Segments

• Setting Storage Parameters for Index Segments

• Changing Values for Storage Parameters

• Understanding Precedence in Storage Parameters

• tablespaces (defaults for any segment in the tablespace)

• tables, clusters, snapshots, and snapshot logs (data segments)

• indexes (index segments)

• rollback segments

Storage Parameters You Can Specify

INITIAL	The size, in bytes, of the first extent allocated when a segment is created.
	Default: 5 data blocks Minimum: 2 data blocks (rounded up) Maximum: operating system-specific
	Although the default system value is given in data blocks, use bytes to set a value for this parameter. You can use the abbreviations K and M to indicate kilobytes and megabytes. Anything less than 2 data blocks is rounded up to the next multiple of the data block size, as determined by the parameter DB_BLOCK_SIZE.
	For example, if the data block size of a database is 2048 bytes, then the system default for the INITIAL storage parameter of tablespaces is 10240 bytes. If you create a tablespace in this database and specify its default storage parameter INITIAL as 20000 (bytes), Oracle automatically rounds this value up to 20480 (10 data blocks).
NEXT	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).
	Default: 5 data blocks Minimum: 1 data block Maximum: operating system-specific
	As with INITIAL, although the default system value is given in data blocks, use bytes to set a value for this parameter. You can use the abbreviations K and M to indicate kilobytes and megabytes. The value is rounded up to the next multiple of the data block size, as determined by the parameter DB_BLOCK_SIZE.
MAXEXTENTS	The total number of extents, including the first, that can ever be allocated for the segment.
	Default: dependent on the data block size and operating system Minimum: 1 (extent) Maximum: unlimited
MINEXTENTS	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.
	Default: 1 (extent) Minimum: 1 (extent) Maximum: operating system-specific
	If MINEXTENTS is greater than 1, then the specified number of incremental extents are allocated at creation time using the values INITIAL, NEXT, and PCTINCREASE.

Note: The default and minimum values of MINEXTENTS for a rollback segment are always 2. If you want to guarantee that you have enough space to load all the data for one table, create the table with a large MINEXTENTS value so that the LOAD operation is successful even if your database is fragmented.

PCTINCREASE	The percent 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.
	Default: 50 (%) Minimum: 0 (%) Maximum: operating system-specific

Note: PCTINCREASE is always 0 for rollback segments. PCTINCREASE cannot be specified for rollback segments.

	By using PCTINCREASE correctly, you can reduce the fragmentation of a segment by enlarging incremental extents and reducing the number of extents that need to be allocated for the segment. The segment contains a few large extents, rather than many smaller extents.
	If you change PCTINCREASE for a segment, the current value of NEXT for that segment does not change. Only future values of NEXT are affected.
INITRANS	Reserves a pre-allocated amount of space for an initial number of transaction entries to access rows in the data block concurrently. Space is reserved in the headers of all data blocks in the associated data or index segment. The default value is 1 for tables and 2 for clusters and indexes.
MAXTRANS	As multiple transactions concurrently access the rows of the same data block, space is allocated for each 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 default value is an operating system-specific function of block size, not exceeding 255.
	If MAXTRANS is too low, transactions blocked by this limit must wait until other transactions complete and free transaction entry space. For example, if MAXTRANS is 3 and a fourth concurrent transaction attempts to access a block already being accessed by three active transactions, Oracle selects one of the three and waits until it commits or rolls back, and then proceeds with the fourth transaction.

See Also: Some defaults are operating system specific; see your operating system-specific Oracle documentation.

Setting INITRANS and MAXTRANS

• 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

Alternatively, assume that a table is usually accessed by many users at the same time. In this case, you might consider pre-allocating transaction entry space by using a high INITRANS (to eliminate the overhead of having to allocate transaction entry space, as required when the object is in use) and allowing a higher MAXTRANS so that no user has to wait to access any necessary data blocks.

Setting Default Storage Parameters for Segments in a Tablespace

Setting Storage Parameters for Data Segments

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 command, rather than the individual CREATE or ALTER commands that put tables and snapshots into the cluster. Storage parameters specified when creating or altering a clustered table or snapshot are ignored. The storage parameters set for the cluster override the table's storage parameters.

Setting Storage Parameters for Index Segments

A PCTFREE setting for an index only has an effect when the index is created. You cannot specify PCTUSED for an index segment.

Changing Values for Storage Parameters

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

1. ALTER TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/INDEX/ROLLBACK SEGMENT statement

2. CREATE TABLE/CLUSTER/SNAPSHOT/SNAPSHOT LOG/CREATE INDEX/ROLLBACK SEGMENT statement

3. ALTER TABLESPACE statement

4. CREATE TABLESPACE statement

5. Oracle default statement

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

Storage Parameter Example

CREATE TABLE test_storage

   ( . . . )

   STORAGE (INITIAL 100K   NEXT 100K

      MINEXTENTS 2   MAXEXTENTS 5

      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:

Extent#	Extent Size	Value for NEXT
1	100K or 50 blocks	100K
2	100K or 50 blocks	CEIL(100K*1.5)=150K
3	150K or 75 blocks	CEIL(150K*1.5)=228K
4	228K or 114 blocks	CEIL(228K*1.5)=342K
5	342K or 171 blocks	CEIL(342K*1.5)=516K
Table 10 - 1. 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 on.