Managing Table Storage in Oracle AI Database 26ai

Separate the storage decisions

“Table storage” is not one setting. Object placement, extent allocation, free-space tracking, and datafile capacity operate at different layers. Keeping those layers separate prevents a clause from being credited with behavior that actually belongs to the tablespace or datafile.

ASSM is not the same as Oracle Automatic Storage Management (ASM). ASSM tracks usable free space inside segments. ASM manages database files across storage devices and disk groups. Neither term is a synonym for AUTOALLOCATE, which is an extent-allocation policy, or AUTOEXTEND, which permits a datafile to grow within configured limits.

Start with a minimal CREATE TABLE statement

The following definition keeps the existing PETSTORE.SALE_ITEM teaching context while relying on current storage defaults. The example assumes that the connected user owns the PETSTORE schema, or otherwise has the required privilege, and has sufficient quota on the USERS tablespace.

CREATE TABLE petstore.sale_item (
    sales_id    NUMBER(10)   NOT NULL,
    product_id  NUMBER(10)   NOT NULL,
    sale_amount NUMBER(10,2)
)
TABLESPACE users;

The TABLESPACE users clause chooses the logical home of the table segment. It does not select a particular datafile or guarantee a particular disk layout. Oracle allocates extents from the tablespace according to that tablespace's policy. A table segment remains in one tablespace, although related index, LOB, partition, or overflow segments can have their own placement.

If TABLESPACE is omitted, Oracle normally uses the creating user's default tablespace, subject to privileges, quota, and local governance. That outcome is deterministic configuration, not a random tablespace choice. The name USERS is illustrative: its allocation policy, file layout, size, and bigfile status must be checked in the actual database.

The example also omits PCTFREE, so the documented default of 10 applies. It omits the STORAGE clause, allowing the locally managed tablespace to control ordinary extent allocation. No NULL keyword is needed for SALE_AMOUNT because nullable is the default when a column is not declared NOT NULL.

Include privileges, quota, and container scope

Naming a tablespace does not by itself authorize storage there. The user needs the privilege required to create the object and enough quota on the selected tablespace, unless an administrator has granted the broader UNLIMITED TABLESPACE system privilege. A table definition can therefore be syntactically correct yet fail because its owner lacks quota. Quota is a per-user capacity control; it is not the same as the table's MAXSIZE or the datafile's maximum size.

A tablespace also does not belong to one schema. It can contain segments owned by several schemas, and one schema can place different segments in different tablespaces. The TABLESPACE clause establishes the location of this table segment, not ownership of the tablespace itself. Separate placement can be selected for indexes, LOB data, partitions, and other associated segments when their definitions require it.

In a multitenant database, interpret users, quotas, tablespaces, and dictionary results in the current container. A local user's USERS tablespace is part of that pluggable database's storage context. The same tablespace name in another PDB does not imply the same files, policy, or capacity. Check the active container before diagnosing a placement or quota problem.

Account for deferred segment creation

A successful CREATE TABLE statement does not always allocate a table segment immediately. With deferred segment creation, Oracle can record the table definition in the data dictionary and postpone its segment until the first row or another operation requires storage. The table can therefore appear in USER_TABLES before it appears in USER_SEGMENTS.

This distinction matters when verifying an INITIAL request. The clause describes the initial segment allocation, but the allocation can occur later if segment creation is deferred. It also explains why an empty result from USER_SEGMENTS immediately after creating an empty table is not necessarily an error.

Understand AUTOALLOCATE, UNIFORM, and ASSM

Locally managed tablespaces track free and used extents through bitmaps. Their extent policy is either system-selected allocation or fixed-size allocation. ASSM solves a different problem: it tracks usable space within the blocks belonging to a segment.

Under AUTOALLOCATE, Oracle determines subsequent extent sizes; a table-level NEXT value does not direct later allocations. Under UNIFORM, the tablespace's uniform size governs them. In either case, an individual extent remains wholly within one datafile, even though different extents of one segment can reside in different files of the same smallfile tablespace.

Reserve block space with PCTFREE

PCTFREE is a whole-number percentage from 0 through 99. It reserves space in each applicable data block for updates that expand rows already stored in the block. With the default value of 10, inserts can fill the block to approximately 90 percent after Oracle's block overhead, leaving the configured reserve available for row growth.

The reserve is not permanently empty. Updates can consume it, and rows can migrate if an expanded row no longer fits. A workload that frequently changes a short value into a much longer value might benefit from a larger reserve. An append-oriented workload with stable row sizes might not. Choose a nondefault value from measured behavior rather than from a generic rule.

In manual segment-space management, PCTFREE sets the upper insertion boundary, while PCTUSED sets the lower threshold at which a block becomes eligible for new inserts again. The sum must not exceed 100 when both apply. The legacy claim that PCTUSED is the maximum percentage before inserts stop reverses its role. Because ASSM ignores PCTUSED, modern table DDL should not tune it as if manual freelists were still controlling free space.

Use INITIAL and MAXSIZE only with intent

Some workloads have a justified reason to request initial capacity or impose an object-level limit. The following example makes both decisions explicit and also reserves additional block space for expanding rows:

CREATE TABLE petstore.sale_item_staged (
    sales_id    NUMBER(10)   NOT NULL,
    product_id  NUMBER(10)   NOT NULL,
    sale_amount NUMBER(10,2)
)
PCTFREE 15
STORAGE (
    INITIAL 8M
    MAXSIZE 1G
)
TABLESPACE users;

INITIAL 8M requests an initial segment size. In an AUTOALLOCATE tablespace, Oracle selects supported extent sizes and allocates enough extents to satisfy the request. In a UNIFORM tablespace, Oracle allocates the number of uniform extents needed. The actual allocation can be rounded to valid boundaries and can exceed the literal request. INITIAL cannot later be supplied in an ALTER statement.

MAXSIZE 1G places an upper bound on the applicable storage element. This is different from limiting the number of extents and from limiting datafile growth. An object-level cap can be useful for a staging object or another controlled workload, but it should not be copied into every table definition without an operational reason.

Interpret classic STORAGE parameters safely

Oracle retains the classic parameters because old applications and specialized objects still contain them. In a locally managed tablespace, accepted syntax does not mean that each value controls the continuing extent sequence.

The historically familiar sequence based on NEXT and PCTINCREASE belongs to dictionary-managed allocation. It should not be taught as the normal growth model for a 26ai table. When legacy DDL includes those values, first query the tablespace policy and then decide whether the compatibility clauses can be removed or whether an initial-size requirement should be expressed more directly.

Modernize legacy storage DDL methodically

Do not delete every storage clause mechanically. First identify the intent behind the old values. Some definitions merely copied a site-wide template and never depended on their extent sequence. Others attempted to preallocate a predictable load, reserve space for expanding rows, or stop a staging object from consuming all available capacity. Preserve the requirement, not the obsolete mechanism used to express it.

1. Query USER_TABLESPACES or DBA_TABLESPACES to establish whether the target is locally managed, whether allocation is system-selected or uniform, and whether segment space management is automatic.
2. Preserve an intentional TABLESPACE choice when that placement still matches the application's storage, security, backup, and operational policies.
3. Remove PCTUSED, FREELISTS, and FREELIST GROUPS from the proposed design when ASSM makes them ineffective. Do not preserve a no-op setting merely because it appears in exported legacy DDL.
4. Replace a complicated INITIAL/NEXT/PCTINCREASE sequence with a single intentional INITIAL request only if the initial allocation still matters. Otherwise, omit the object-level extent settings.
5. If the old MAXEXTENTS value was intended as a capacity safeguard, determine whether a meaningful MAXSIZE, quota, or service-level capacity policy is the correct modern control.
6. Test the revised DDL with representative data, then measure actual segment size, extent allocation, row expansion, and workload behavior.

This process avoids two opposite errors: blindly retaining settings that Oracle now ignores and blindly removing a value that represented a real operational boundary. The data dictionary supplies the configuration evidence, while workload testing determines whether a nondefault choice is beneficial.

Distinguish object limits from datafile limits

The word MAXSIZE can appear in different storage contexts. Inside an object's STORAGE clause, it limits that storage element. In a datafile specification such as AUTOEXTEND ON ... MAXSIZE ..., it limits how far that datafile can grow. Increasing a datafile does not change a table's object-level limit, and increasing a table's limit does not create physical capacity.

Oracle AI Database 26ai makes BIGFILE the default for newly created SYSAUX, SYSTEM, and USER tablespaces when those defaults are not explicitly overridden. A bigfile tablespace contains one large datafile, while a smallfile tablespace can contain multiple datafiles. Existing upgraded databases and explicitly configured installations can differ, so the current dictionary remains the authority.

Verify the tablespace before interpreting the DDL

The following queries are intended for the current table owner, such as a session connected as PETSTORE. Begin with the tablespace; otherwise, a compatibility value displayed for the table can be mistaken for the policy that controls future allocations.

SELECT tablespace_name,
       extent_management,
       allocation_type,
       segment_space_management,
       bigfile
FROM   user_tablespaces
WHERE  tablespace_name = 'USERS';

EXTENT_MANAGEMENT = 'LOCAL' confirms local management. ALLOCATION_TYPE = 'SYSTEM' identifies system-selected allocation, while UNIFORM identifies fixed extents. SEGMENT_SPACE_MANAGEMENT = 'AUTO' confirms ASSM, and BIGFILE = 'YES' identifies the single-datafile form. Do not assume these values merely from the tablespace name.

Next, inspect the table attributes recorded for the current owner:

SELECT table_name,
       tablespace_name,
       pct_free,
       pct_used,
       ini_trans,
       initial_extent,
       next_extent,
       max_extents
FROM   user_tables
WHERE  table_name = 'SALE_ITEM';

Data-dictionary columns can retain compatibility-oriented values even when the locally managed tablespace determines later allocations. For example, a displayed NEXT_EXTENT value must not be interpreted in isolation as a promise that every future extent will have that size.

After the segment exists, inspect the storage actually allocated to it:

SELECT segment_name,
       segment_type,
       tablespace_name,
       bytes,
       blocks,
       extents
FROM   user_segments
WHERE  segment_name = 'SALE_ITEM';

If the table is still using deferred segment creation, this query can return no row. After allocation, it reports the segment's current total bytes, blocks, and extent count rather than a forecast derived from old DDL.

To examine the individual allocated extents, query:

SELECT segment_name,
       extent_id,
       bytes,
       blocks
FROM   user_extents
WHERE  segment_name = 'SALE_ITEM'
ORDER  BY extent_id;

The results depend on the executed DDL, the tablespace policy, deferred allocation, and subsequent activity. For that reason, this lesson does not invent fixed result rows or promise a particular number of extents.

A practical storage decision checklist

1. Choose the tablespace that should own the table segment, or confirm that the user's default is appropriate.
2. Verify that the tablespace is locally managed and determine whether allocation is system-selected or uniform.
3. Verify whether ASSM manages free space inside its segments.
4. Keep the default PCTFREE unless measured row expansion supports a different reserve.
5. Request an INITIAL size only when deliberate initial allocation has a documented benefit.
6. Set an object-level MAXSIZE only when a defined capacity boundary is required.
7. Interpret legacy parameters through the current tablespace policy instead of copying them into new DDL.
8. Verify the table and segment through the dictionary after creation and after representative data is loaded.

These choices control where a table segment can be allocated and how its space can grow. They do not define whether the stored rows are valid. The next lesson moves from physical organization to logical integrity by showing how a primary key identifies each row and protects uniqueness.

Creating Oracle Tables - Quiz