STORAGE clause

Purpose

Prerequisites

• clusters

• indexes

• rollback segments

• snapshots

• snapshot logs

• tables

• tablespaces

Syntax

Keywords and Parameters

INITIAL

specifies the size in bytes of the object's first extent. Oracle7 allocates space for this extent when you create the object. You can also 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. The maximum value varies depending on your operating system. Oracle7 rounds values up to the next multiple of the data block size for values less than 5 data blocks. Oracle7 rounds values up to the next multiple of 5 data blocks.

NEXT

specifies the size in bytes of the next extent to be allocated to the object. You can also 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 varies depending on your operating system. Oracle7 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, Oracle7 rounds up to a value than minimizes fragmentation, as described in the "Data Blocks, Extents, and Segments" chapter of Oracle7 Server Concepts.

PCTINCREASE

specifies the percent by which each extent after the second grows over the previous 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 varies depending on your operating system.

You cannot specify PCTINCREASE for rollback segments. Rollback segments always have a PCTINCREASE value of 0.

Oracle7 rounds the calculated size of each new extent up to the next multiple of the data block size.

MINEXTENTS

specifies the total number of extents to allocate when the object is created. This parameter allows 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 Oracle7 only allocates the initial extent, except for rollback segments for which the default and minimum value is 2. The maximum value varies depending on your operating system.

If the MINEXTENTS value is greater than 1, then Oracle7 calculates the size of subsequent extents based on the values of the INITIAL, NEXT, and PCTINCREASE parameters.

MAXEXTENTS

specifies the total number of extents, including the first, that Oracle7 can allocate for the object. The minimum value is 1. The default and maximum values vary depending your data block size.

UNLIMITED specifies that extents should automatically be allocated as needed. You should not use this option for rollback segments.

FREELIST GROUPS

for objects other than tablespaces, specifies the number of groups of free lists for a table, cluster, or index. The default and minimum value for this parameter is 1. Only use this parameter if you are using Oracle7 with the Parallel Server option in parallel mode.

FREELISTS

for objects other than tablespaces, specifies the number of free lists for each of the free list groups for the table, 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 FREELISTS value that is too large, Oracle7 returns an error message indicating the maximum value.

You can only specify the FREELISTS parameter in CREATE TABLE, CREATE CLUSTER, and CREATE INDEX statements. You can only specify the FREELIST GROUPS parameter in CREATE TABLE and CREATE CLUSTER statements.

OPTIMAL

specifies an optimal size in bytes for a rollback segment. Not applicable to other kinds of objects. You can also use K or M to specify this size in kilobytes or megabytes. Oracle7 tries to maintain this size for the rollback segment by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle7 deallocates as many extents as possible without reducing the total size of the rollback segment below the OPTIMAL value.

NULL specifies no optimal size for the rollback segment, meaning that Oracle7 never deallocates the rollback segment's extents. This is the default behavior.

The value of this parameter cannot be less than the space initially allocated for the rollback segment specified by the MINEXTENTS, INITIAL, NEXT, and PCTINCREASE parameters. The maximum value varies depending on your operating system. Oracle7 rounds values to the next multiple of the data block size.

Usage Notes

When you create a tablespace, you can specify values for the STORAGE parameters. These values serve as default STORAGE parameter values for segments allocated in the tablespace.

When you create a cluster, index, rollback segments, snapshot, snapshot log, or table, you can specify values for the STORAGE parameters for the segments allocated to these objects. If you omit any STORAGE parameter, Oracle7 uses the value of that parameter specified for the tablespace.

When you alter a cluster, index, rollback segment, snapshot, snapshot log, or table, you can change the values of STORAGE parameters. These new values only affect future extent allocations. For this reason, you cannot change the values of the INITIAL and MINEXTENTS parameter. If you change the value of the NEXT parameter, the next allocated extent will have the specified size, regardless of the size of the most-recently allocated extent and the value of the PCTINCREASE parameter. If you change the value of the PCTINCREASE parameter, Oracle7 calculates the size of the next extent using this new value and the size of the most recently allocated extent.

When you alter a tablespace, you can change the values of STORAGE parameters. These new values serve as default values only to subsequently allocated segments (or subsequently created objects).

ROLLBACK SEGMENTS and MAXEXTENTS UNLIMITED

A rollback segment created without specifying the storage option has the same storage options as the tablespace that the rollback segment is created in. Thus, if the tablespace is created with MAXEXTENT UNLIMITED, then the rollback segment would also have the same default.

Example I

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 ) 

Oracle7 allocates space for the table based on the STORAGE parameter values for the following reasons:

• Because the MINEXTENTS value is 1, Oracle7 allocates 1 extent for the table upon creation.

• Because the INITIAL value is 100K, the first extent's size is 100 kilobytes.

• If the table data grows to exceed the first extent, Oracle7 allocates a second extent. Because the NEXT value is 50K, the second extent's size is 50 kilobytes.

• If the table data subsequently grows to exceed the first two extents, Oracle7 allocates a third extent. Because the PCTINCREASE value is 5, 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, Oracle7 rounds this value to 52 kilobytes.

If the table data continues to grow, Oracle7 allocates more extents, each 5% larger than the previous one.

• Because the MAXEXTENTS value is 50, Oracle7 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 ) 

Oracle7 allocates space for the rollback segment based on the STORAGE parameter values:

• Because the MINEXTENTS value is 2, Oracle7 allocates 2 extents for the rollback segment upon creation.

• Because the INITIAL value is 10K, the first extent's size is 10 kilobytes.

• Because the NEXT value is 10K, the second extent's size is 10 kilobytes.

• If the rollback data exceeds the first two extents, Oracle7 allocates a third extent. Because the PCTINCREASE value for rollback segments is always 0, the third extent is the same size as the second extent, 10 kilobytes.

If the rollback data continues to grow, Oracle7 allocates more extents, each the same size as the previous one, 10 kilobytes.

• Because the MAXEXTENTS value is 25, Oracle7 can allocate as many as 25 extents for the rollback segment.

• Because the OPTIMAL value is 50K, Oracle7 deallocates extents if the rollback segment exceeds 50 kilobytes. Note that Oracle7 only deallocates extents that contain data for transactions that are no longer active.

Related Topics

TRUNCATE

Purpose

Prerequisites

If you are using Trusted Oracle, your DBMS label must match the creation label of the table or cluster or you must satisfy one of these criteria. If the creation label of the table or cluster is not comparable or higher than your DBMS label, you must have READUP system privilege.

Syntax

Keywords and Parameters

TABLE

specifies the schema and name of the table to be truncated. If you omit schema, Oracle7 assumes the table is in your own schema. This table cannot be part of a cluster.

When you truncate a table, Oracle7 also automatically deletes all data in the table's indexes.

CLUSTER

specifies the schema and name of the cluster to be truncated. If you omit schema, Oracle7 assumes the cluster is in your own schema. You can only truncate an indexed cluster, not a hash cluster.

When you truncate a cluster, Oracle7 also automatically deletes all data in the cluster's tables' indexes.

DROP STORAGE

deallocates the space from the deleted rows from the table or cluster. This space can subsequently be used by other objects in the tablespace.

REUSE STORAGE

leaves the space from the deleted rows allocated to the table or cluster. STORAGE values are not reset to the values when the table or cluster was created. This space can be subsequently used only by new data in the table or cluster resulting from inserts or updates.

The DROP STORAGE or REUSE STORAGE option that you choose also applies to the space freed by the data deleted from associated indexes.

If you omit both the REUSE STORAGE and DROP STORAGE options, Oracle7 uses the DROP STORAGE option by default.

Usage Notes

• The TRUNCATE command is a Data Definition Language command and generates no rollback information.

• Truncating a table does not fire the table's DELETE triggers.

• Truncating the master table of a snapshot does not record any changes in the table's snapshot log.

Deleting rows with the TRUNCATE command is also more convenient than dropping and recreating a table for the following reasons:

• Dropping and recreating invalidates the table's dependent objects, while truncating does not.

• Dropping and recreating requires you to regrant object privileges on the table, while truncating does not.

• Dropping and recreating requires you to recreate the table's indexes, integrity constraints, and triggers and respecify its STORAGE parameters, while truncating does not.

You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and recreate the table.

You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table.

If you truncate the master table of a snapshot, Oracle7 does not record the removed rows in the snapshot log. For this reason, a fast refresh does not remove the rows from the snapshot. Snapshots based on a truncated table must be refreshed completely for Oracle7 to remove their rows.

You cannot roll back a TRUNCATE statement.

Example I

The following statement deletes all rows from the EMP table and returns the freed space to the tablespace containing EMP:

TRUNCATE TABLE emp 

The above statement also deletes all data from all indexes on EMP and returns the freed space to the tablespaces containing them.

Example II

The following statement deletes all rows from all tables in the CUST cluster, but leaves the freed space allocated to the tables:

TRUNCATE CLUSTER cust 

	REUSE STORAGE 

The above statement also deletes all data from all indexes in the tables in CUST.

Related Topics