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
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
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
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:
[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,
* Oracle Administrator, online reference,
RevealNet, Inc., version 01-3.
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.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.