Remote DBA Plans
Remote DBA Service

The STORAGE Clause

Oracle Tips by Burleson Consulting

The STORAGE clause is used in table, cluster, index, rollback segment, snapshot, snapshot log, and tablespace creation commands, as well as in their respective ALTER commands. Let’s look at the parameters for this clause, as Remote DBAs must become intimately familiar with all aspects of the STORAGE clause.

Note: The syntax for the STORAGE clause is shown in detail in the SQL Reference Manual on the documentation Web site.

STORAGE Parameters: Definitions

INITIAL. Sets the size in bytes, kilobytes, or megabytes for the initial extent for the object. This should be set to hold the first year’s worth of expected data. If you will be loading data using sqlloader parallel inserts, set the initial extent to the size expected for one year, divided by the number of parallel processes, and set NEXT to the same value. This is suggested because all parallel insert processes insert to their own extents. The Oracle process will round up to the next multiple of data blocksize for sizes smaller than five data blocks. The minimum size is two data blocks. The maximum size is operating-system specific.

NEXT. Sets the value for the next extent of the file. It is specified in bytes, kilobytes, or megabytes. The default value is five data blocks. The minimum is one data block. Oracle rounds up to the next whole blocksize for sizes less than five blocks. The maximum size is operating-system specific. For sizes over five blocks, Oracle will resize to minimize fragmentation if possible.

PCTINCREASE. A value is from 0-100; sets the amount that each extension after NEXT will increase in size over the size of NEXT. This factor is applied to the last extent created; it is not calculated based on the size of NEXT after the first extension after NEXT. The default is 50. If you properly size your tables, this should be set to 0. This factor cannot be set for rollback segments. For rollback segments, this factor will be set to 0.

MINEXTENTS. Sets how many initial extents Oracle will create for a specified object. Generally, this is set to the default of 1 (2 for rollback segments). If you use parallel insert processes, you may want to adjust INITIAL, NEXT, PCTINCREASE, and MINEXTENTS to set the size of initial extents to the size corresponding to calculated table size, divided by number of insert processes, and the MINEXTENTS to the number of insert processes. The value for the sizes of the extents is calculated based on INITIAL, NEXT, and PCTINCREASE.

MAXEXTENTS. Determines the maximum number of extents allowed a specific object. The minimum value is 1; the maximum is determined by the size of your data blocksize.

UNLIMITED. Means the object can grow until it runs out of space. This setting is not suggested for use with rollback segments.

FREELIST GROUPS. Used for objects other than tablespaces to set up the number of groups of FREELISTS. The default and minimum for this parameter is 1. Only use this parameter with the parallel server set in parallel mode. This parameter is only used in tables and clusters.

FREELISTS. Used for objects other than tablespaces. The default is 1; the maximum is dependent on your data blocksize. If multiple processes will be updating the same data block, this parameter should be set higher. This parameter is only used in tables, indexes, and clusters.

OPTIMAL. Used with rollback segments and sets the size in bytes, kilobytes, or megabytes for the optimal size of a rollback segment. This is the size that the rollback segment will shrink to when it has expanded because of a large transaction. This cannot be set to less than the amount of space used by the rollback segment via the INITIAL, NEXT, and MINEXTENTS values.

BUFFER_POOL. Has three possible values: KEEP, RECYCLE, and DEFAULT. BUFFER_POOL allows the Remote DBA to specify which buffer pool will hold the data from the object. If KEEP is specified for BUFFER_POOL, then the data is protected from LRU aging. If RECYCLE is specified for the BUFFER_POOL, the data experiences accelerated aging. If no value is set for BUFFER_POOL, or DEFAULT is specified, then behavior is identical to previous releases when multiple buffer pools weren’t available.

The High-Water Mark

To view an object’s high-water mark, 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.

Some operations, such as parallel inserts, will only insert into space above the high-water mark. It may be smart to reduce space used to the absolute minimum and then reset NEXT and PCTINCREASE before performing a large parallel insert.

Within a segment, the high-water mark indicates the amount of used space. You cannot release space below the high-water mark (even if there is no data in the space you wish to deallocate). However, if the segment is completely empty, you can release space using the TRUNCATE object DROP STORAGE statement.

The Oracle9i PFILE and SPFILE Commands

In Oracle9i, the ability to create parameter (initialization) files (called pfiles) and server parameter files (dynamic initialization) called spfiles was added. The commands are:

CREATE PFILE [pfile_name] FROM [spfile_name];
CREATE SPFILE [spfile_name] FROM PFILE [pfile_name];

If the pfile_name and spfile_names aren’t provided, they will be given default names and stored in the default locations for your system.

A PFILE is a standard Oracle initialization file. However, the ability to simply create one from the database was not provided until Oracle9i. An SPFILE, on the other hand, is a feature new to Oracle9i; it allows the Remote DBA to create a binary file that tracks any ALTER SYSTEM commands that change systemwide initialization parameters (for a complete list, refer back to Chapter 2) and makes it possible to capture and reuse these dynamic changeson the next database startup.

By allowing a PFILE to be created from an SPFILE, Oracle gives a Remote DBA a method for easily dumping the database initialization parameters, editing them, and then reloading them by inverting the command.

Further Remote DBA Reading

The Remote DBA should consult the following references for more detailed information:

* Oracle9i Database Administrator’s Guide, Release 1 (9.0.1), Part No. A90117-01, Oracle Corporation, June 2001.

* Oracle9i SQL Reference, Release 1 (9.0.1), Part No. A90125-01, Oracle Corporation, June 2001.

* Oracle9i Performance Guide and Reference, Release 1 (9.0.1), Part no. A87503-01, Oracle Corporation, June 2001.

* Oracle9i JDBC Developer’s Guide and Reference, Release 1 (9.0.1), Part No. A90211-01, Oracle Corporation, June 2001.

* Oracle Administrator, online reference, RevealNet, Inc., version 01-3. www.revealnet.com.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.