Use the Oracle "storage clause" to set the "extent size" for a table

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.
In Oracle, the "STORAGE" clause is used to specify storage attributes for schema objects, such as tables or indexes. You can use it to set the extent size, among other parameters. The extent size is controlled by the `INITIAL`, `NEXT`, `PCTINCREASE`, `MINEXTENTS`, and `MAXEXTENTS` parameters within the `STORAGE` clause.
Here's how you can use the `STORAGE` clause to set the extent size for a table:
Syntax

CREATE TABLE table_name (
    column_name data_type,
    ...
)
STORAGE (
    INITIAL size_in_bytes
    NEXT size_in_bytes
    PCTINCREASE percentage
    MINEXTENTS number
    MAXEXTENTS number | UNLIMITED
);

Explanation of Parameters

1. INITIAL: Specifies the size of the first extent when the table is created.
2. NEXT: Specifies the size of the second extent (and subsequent extents, if PCTINCREASE is 0).
3. PCTINCREASE: Defines the percentage by which the size of each new extent grows. Set it to 0 for uniform extent sizes.
4. MINEXTENTS: The minimum number of extents to allocate initially.
5. MAXEXTENTS: The maximum number of extents the object can have. Use UNLIMITED for no limit.

Example
To create a table with custom extent sizes:

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    salary NUMBER
)
STORAGE (
    INITIAL 1M
    NEXT 512K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
);

Explanation of Example

1. The initial extent size is 1MB.
2. The next extent size is 512KB.
3. No growth is applied to the extent sizes (PCTINCREASE 0).
4. At least 1 extent is allocated when the table is created.
5. The table can grow to an unlimited number of extents.

Best Practices

• Use uniform extent sizes (PCTINCREASE 0) to maintain consistent performance and manageability.
• Consider tablespaces with automatic extent management (introduced in Oracle 8i) for better optimization and less manual tuning. This approach simplifies storage management by avoiding manual settings in the STORAGE clause.

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.

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 );

The statement above is an example of using the "Oracle `STORAGE` clause". The `STORAGE` clause specifies storage parameters that control how space is allocated for a segment (like a table, index, or other schema objects) in the database.
Explanation of the `STORAGE` Clause Parameters:
Here is the breakdown of the `STORAGE` clause in your SQL statement:

STORAGE  (
    INITIAL 100K       -- Specifies the initial amount of space allocated to the segment (100 KB).
    NEXT 50K           -- Specifies the size of the next extent allocated to the segment (50 KB).
    MINEXTENTS 1       -- Specifies the minimum number of extents allocated to the segment (1 extent).
    MAXEXTENTS 5       -- Specifies the maximum number of extents that can be allocated to the segment (5 extents).
    PCTINCREASE 50     -- Specifies the percentage by which the size of each subsequent extent grows (50% increase for each new extent).
);

What This Does:

• INITIAL: Allocates 100 KB of storage when the table is created.
• NEXT: Specifies that additional extents will be 50 KB in size.
• MINEXTENTS: Ensures at least 1 extent is created at the time of table creation.
• MAXEXTENTS: Limits the table to a maximum of 5 extents.
• PCTINCREASE: Controls how the size of subsequent extents grows, with each new extent being 50% larger than the previous one.

Notes:

1. The STORAGE clause is typically used when you need fine-grained control over how Oracle allocates space for the object.
2. However, explicit use of the STORAGE clause has become less common with modern versions of Oracle Database due to automatic space management features like Automatic Segment Space Management (ASSM) and Uniform Extent Allocation.
3. If you are working with an Oracle version supporting automatic space management, the STORAGE clause might be overridden or ignored depending on tablespace settings.

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.

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.