Space Management   «Prev  Next»

Lesson 6Setting the extent size for an object
ObjectiveUse the storage clause to set the extent size for a table in Oracle

Setting Oracle extent size

Use the storage clause to set the extent size for a table in Oracle.
In the previous lesson, you learned about the different attributes that determine how Oracle uses extents within a segment. There are a set of SQL keywords you can use in the DDL statements that create and alter database objects and thus control how extents are used.
Question: How do I use the storage clause to set the extent size for a table in Oracle?
In Oracle Database Management System, you can use the STORAGE clause to define the storage characteristics of a table, including the extent size. The STORAGE clause allows you to set parameters such as INITIAL, NEXT, PCTINCREASE, MINEXTENTS, and MAXEXTENTS, which control how the table's extents are allocated and resized as the table grows.
To set the extent size for a table using the STORAGE clause, follow these steps:

1. Define the INITIAL and NEXT parameters:

2. Set the PCTINCREASE parameter:

The PCTINCREASE parameter controls the growth rate of the extent size for the table. It is expressed as a percentage of the size of the previous extent. A PCTINCREASE value of 0 indicates that each subsequent extent will have a fixed size equal to the NEXT parameter value. To set the PCTINCREASE value to 50%, for example, you would use the following syntax:

PCTINCREASE 50

3. Specify the MINEXTENTS and MAXEXTENTS parameters:

The MINEXTENTS parameter defines the minimum number of extents that the table should have initially. The MAXEXTENTS parameter determines the maximum number of extents the table can have.
To set the MINEXTENTS value to 1 and the MAXEXTENTS value to 100, for example, you would use the following syntax:
MINEXTENTS 1 MAXEXTENTS 100

4. Incorporate the STORAGE clause into the CREATE TABLE statement:

To create a table with the specified storage parameters, include the STORAGE clause in the CREATE TABLE statement, as shown in the following example:
CREATE TABLE sample_table (
  id NUMBER,
  name VARCHAR2(100)
)
STORAGE (
  INITIAL 1M
  NEXT 2M
  PCTINCREASE 50
  MINEXTENTS 1
  MAXEXTENTS 100
);

By using the STORAGE clause with the appropriate parameters, you can set the extent size for a table in an Oracle database, as well as control the growth pattern of the extents as the table expands. This allows you to optimize the storage allocation and performance of your database objects.

Syntax for extents

All of these storage parameters can be used with a
CREATE TABLE, CREATE INDEX, ALTER TABLE
, or
ALTER INDEX
statement:

INITIAL: This keyword is used with an integer and either K or M, to specify either Kilobytes or Megabytes. The INITIAL size is the size of the initial extent allocated when the object is created.
NEXT: This keyword uses the same integer notation as INITIAL and describes the size of the first extent allocated after the initial extent is full.
PCTINCREASE: This keyword specifies the percentage each subsequent extent should grow by. If the NEXT size is 20 K and the PCTINCREASE is 50 (its default value), the third extent allocated would be 30 K (20 K * 150%), the fourth extent allocated would be 45 K (30 K * 150%), and so on.
MINEXTENTS: This keyword is followed by an integer that indicates the minimum number of extents that are initially allocated. If the value for this parameter is greater than 1, Oracle will allocate the additional extents when the object is created, based on the INITIAL, NEXT, and PCTINCREASE parameters.
MAXEXTENTS: This parameter limits the overall size of the database object.

Example

View the code below.
CREATE TABLE COINS_IN_LOT (auction_id NUMBER, lot_id NUMBER, coin_id NUMBER) 
STORAGE  (INITIAL 100K  NEXT 50K  MINEXTENTS 1  MAXEXTENTS 5  PCTINCREASE 50 );

If you use the storage parameters when creating a table, the syntax will look like this: This SQL statement causes the table to begin with a single extent of 100 K allocated. Subsequent extents would contain 50K, 75 K (50 K *150%), and so on. The table could not contain more than five extents, which would limit its overall size to 506.25 K.
You do not have to specify storage parameters for any object.

Implications of the storage Clause

The amount of space used by a segment is determined by its storage parameters. These parameters are determined by the database at segment-creation time; if no specific storage parameters are given in the
  1. create table,
  2. create index, or
  3. create cluster
command, then the database will use the default storage parameters for the tablespace in which the segment is to be stored.
Note: You can assign default tablespaces to users, and assign space quotas within those tablespaces, via the create user, alter user, and grant commands. When you create a table, index, or other segment, you can use the default values for a locally managed tablespace (the recommended option) or specify a storage clause as part of the create command. You can also specify a tablespace clause, enabling you to direct Oracle to store the data in a particular tablespace. For example, a create table command in a dictionary-managed tablespace may include the following clauses:
tablespace USERS
storage (initial 1M next 1M pctincrease 0
minextents 1 maxextents 200)

If no storage clause is specified, the default storage parameters of the tablespace will be used. For a locally managed USERS tablespace, the create table command would only need to include

tablespace USERS

The storage parameters specify the initial extent size, the next extent size, the pctincrease (a factor by which each successive extent will geometrically grow),
  1. the maxextents (maximum number of extents), and
  2. the minextents (minimum number of extents).
After the segment has been created, the initial and minextents values cannot be altered unless you perform a reorganization of the object. The default values for the storage parameters for each tablespace are available in the DBA_TABLESPACES and USER_TABLESPACES views.
The next lesson shows how to set default storage attributes for all the objects in a tablespace.