René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Storage clause

storage (
  initial           65536 
  next            1048576 
  minextents            1 
  maxextents   2147483645
  pctincrease           0 
  freelists             1 
  freelist groups       1 
  optimal              7k
  buffer_pool default
)
The storage clause appears within the lob parameters and physical attributes clause.
A create tablespace statement allows to have a default storage clause that specifies the default storage for objects created within the created tablespace. However, a default storage clause can not be specified for locally managed tablespaces. Dictionary managed tablespaces allow to have a storage clause, but without freelists, freelist groups and buffer_pool.

initial

Specifies the size (in bytes) of the first extent.

next

Specifies the size (in bytes) of the secondextent.

pctincrease

Specifies the size of the nth extent. size of nth extent = pctincrease * size of (n-1)th extent.
pctincrease should be set to 0 in order to reduce fragmentation on the tablespace.

minextents

Specifies the initial number of extents when the object is created.

maxextents

Specifies the maximum number of extents that the object can have.

freelists

Specifies the number of freelists.
freelists can only be specified in a create table or create index (and create cluster, create materialized view and create materialized view log ?) statement, otherwise an ORA-02169: FREELISTS storage option not allowed is returned.

freelist groups

Specifies the number of freelist groups.
freelist groups can only be specified in create table or create index (and create cluster, create materialized view and create materialized view log ?) statement, otherwise an ORA-02170: FREELIST GROUPS storage option not allowed is returned.

buffer_pool

The value for buffer_pool must be one of
  • keep
  • recycle
  • default
This parameter can only be specified for a create table, create index, create cluster, alter table, alter index and alter cluster statement.

optimal

The optimal specifier can only be used for rollback segments.