Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 3 of 5
To specify storage characteristics for any of the following schema objects:
Storage parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. For a discussion of the effects of these parameters, see Oracle8i Designing and Tuning for Performance.
When you create a tablespace, you can specify values for the storage parameters. These values serve as default values for segments allocated in the tablespace.
When you alter a tablespace, you can change the values of storage parameters. The new values serve as default values only for subsequently allocated segments (or subsequently created objects).
Note:
The storage_clause is interpreted differently for locally managed tablespaces. At creation, Oracle ignores |
When you create a cluster, index, rollback segment, snapshot, snapshot log, table, or partition, you can specify values for the storage parameters for the segments allocated to these objects. If you omit any storage parameter, Oracle uses the value of that parameter specified for the tablespace.
When you alter a cluster, index, rollback segment, snapshot, snapshot log, table, or partition, you can change the values of storage parameters. The new values affect only future extent allocations.
To change the value of a STORAGE
parameter, you must have the privileges necessary to use the appropriate CREATE
or ALTER
statement.
|
specifies in bytes the size of the object's first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks for nonbitmapped segments or 3 data blocks for bitmapped segments, plus one data block for each free list group you specify (see "FREELIST GROUPS"). The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks, and rounds up to the next multiple of 5 data blocks for values greater than 5 data blocks.
Restriction: You cannot specify |
|
|
specifies in bytes the size of the next extent to be allocated to the object. Use K or M to specify the size in kilobytes or megabytes. The default value is the size of 5 data blocks. The minimum value is the size of 1 data block. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks. For values greater than 5 data blocks, Oracle rounds up to a value that minimizes fragmentation, as described in Oracle8i Concepts.
If you change the value of the |
|
|
specifies the percent by which the third and subsequent extents grow over the preceding extent. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The maximum value depends on your operating system. |
|
|
Oracle rounds the calculated size of each new extent to the nearest multiple of the data block size.
If you change the value of the |
|
|
Suggestion: If you wish to keep all extents the same size, you can prevent SMON from coalescing extents by setting the value of |
|
|
Restriction: You cannot specify |
|
|
specifies the total number of extents to allocate when the object is created. This parameter enables you to allocate a large amount of space when you create an object, even if the space available is not contiguous. The default and minimum value is 1, meaning that Oracle allocates only the initial extent, except for rollback segments, for which the default and minimum value is 2. The maximum value depends on your operating system. |
|
|
If the
Restriction: You cannot specify |
|
|
specifies the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on your data block size. |
|
|
|
specifies that extents should be allocated automatically as needed. Oracle Corporation recommends this setting as a way to minimize fragmentation. However, do not use this clause for rollback segments. Rogue transactions containing inserts, updates, or deletes that continue for a long time will continue to create new extents until a disk is full. |
|
|
Caution: A rollback segment that you create without specifying the storage_clause has the same storage parameters as the tablespace that the rollback segment is created in. Thus, if you create the tablespace with |
|
specifies the number of groups of free lists for the database object you are creating. The default and minimum value for this parameter is 1. Oracle uses the instance number of Oracle Parallel Server instances to map each instance to one free list group.
Each free list group uses one database block. If you do not specify a large enough value for See Also: Oracle8i Parallel Server Concepts. |
|
|
Restriction: You can specify the |
|
|
for objects other than tablespaces, specifies the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If you specify a |
|
|
Restriction: You can specify |
|
|
is relevant only to rollback segments. It specifies an optimal size in bytes for a rollback segment. Use K or M to specify this size in kilobytes or megabytes. Oracle tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segment below the |
|
|
|
specifies no optimal size for the rollback segment, meaning that Oracle never deallocates the rollback segment's extents. This is the default behavior. |
|
The value of |
|
|
defines a default buffer pool (cache) for a schema object. All blocks for the object are stored in the specified cache. If a buffer pool is defined for a partitioned table or index, then the partitions inherit the buffer pool from the table or index definition, unless overridden by a partition-level definition. |
|
|
Note: See Also: Oracle8i Designing and Tuning for Performance for more information about using multiple buffer pools. |
|
|
|
retains the schema object in memory to avoid I/O operations. |
|
|
eliminates blocks from memory as soon as they are no longer needed, thus preventing an object from taking up unnecessary cache space. |
|
|
always exists for objects not assigned to |
The following statement creates a table and provides storage parameter values:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) ) STORAGE ( INITIAL 100K NEXT 50K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 5);
Oracle allocates space for the table based on the STORAGE parameter values as follows:
MINEXTENTS
value is 1, so Oracle allocates 1 extent for the table upon creation.
INITIAL
value is 100K, so the first extent's size is 100 kilobytes.
NEXT
value is 50K, so the second extent's size would be 50 kilobytes.
PCTINCREASE
value is 5, so the calculated size of the third extent is 5% larger than the second extent, or 52.5 kilobytes. If the data block size is 2 kilobytes, Oracle rounds this value to 52 kilobytes.
If the table data continues to grow, Oracle allocates more extents, each 5% larger than the previous one.
MAXEXTENTS
value is 50, so Oracle can allocate as many as 50 extents for the table.
The following statement creates a rollback segment and provides storage parameter values:
CREATE ROLLBACK SEGMENT rsone STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 25 OPTIMAL 50K );
Oracle allocates space for the rollback segment based on the STORAGE
parameter values as follows:
MINEXTENTS
value is 2, so Oracle allocates 2 extents for the rollback segment upon creation.
INITIAL
value is 10K, so the first extent's size is 10 kilobytes.
NEXT
value is 10K, so the second extent's size is 10 kilobytes.
PCTINCREASE
value for rollback segments is always 0, so the third and subsequent extents are the same size as the second extent, 10 kilobytes.
MAXEXTENTS
value is 25, so Oracle can allocate as many as 25 extents for the rollback segment.
OPTIMAL
value is 50K, so Oracle deallocates extents if the rollback segment exceeds 50 kilobytes. Oracle deallocates only extents that contain data for transactions that are no longer active.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|