What Are Deferred Segments? Usage and Caveats

In Oracle Database, when you create a table, a segment (the physical storage area for data) is usually allocated immediately. By leveraging Deferred Segment Creation, you can create segments only when they are actually needed and utilize storage more efficiently.

This article explains the basics of deferred segments, practical benefits and caveats, and tips to avoid common pitfalls for beginners.

What are deferred segments?

Deferred segments are a mechanism where no physical storage is allocated at table creation time; instead, the segment is created only when data is first inserted. This feature was introduced in Oracle Database 11g and is enabled by default.

Why are deferred segments useful?

Storage savings
Prevents wasting space on tables that may never be used.
Efficiency during initialization
Reduces the load of segment creation when creating a large number of tables in bulk.
Simplified management
Storage usage is clearer because segments are not generated for unused tables.

How deferred segments work

This section compares behavior when deferred segments are enabled vs. disabled.

When deferred segments are enabled

Table creation: No segment is created.
Data insertion: The segment is created for the first time at this point.

When deferred segments are disabled

Table creation: A segment is created immediately.
Data insertion: Data is stored in the already-created segment.

How to verify

Use the following SQL to check segment creation status for a table.

SELECT TABLE_NAME, SEGMENT_CREATED
FROM DBA_TABLES
WHERE TABLE_NAME = 'table_name';

SEGMENT_CREATED = NO: deferred segment state
SEGMENT_CREATED = YES: segment already created

SQL> SELECT OWNER,TABLE_NAME, SEGMENT_CREATED FROM DBA_TABLES
  2  WHERE OWNER='SCOTT';
OWNER                TABLE_NAME                     SEGMENT_C
-------------------- ------------------------------ ---------
SCOTT                DEPT                           YES
SCOTT                EMP                            YES
SCOTT                BONUS                          NO
SCOTT                SALGRADE                       YES

Configuration and administration

Deferred segments are controlled by the DEFERRED_SEGMENT_CREATION initialization parameter.

Check the current setting

SHOW PARAMETER DEFERRED_SEGMENT_CREATION;

The default is TRUE.

Change the setting

Enable deferred segments

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = TRUE;

Disable deferred segments

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE;

Practical usage

Hands-on: observe deferred segment behavior

Step 1: Create a table

CREATE TABLE example_table (
    id NUMBER,
    name VARCHAR2(100)
);

At this point, no segment is created.

Step 2: Check segment creation status

SELECT TABLE_NAME, SEGMENT_CREATED
FROM USER_TABLES
WHERE TABLE_NAME = 'EXAMPLE_TABLE';

Result:

TABLE_NAME SEGMENT_CREATED
EXAMPLE_TABLE NO

Step 3: Insert data

INSERT INTO example_table VALUES (1, 'Alice');

The segment is created upon data insertion.

Step 4: Re-check

SELECT TABLE_NAME, SEGMENT_CREATED
FROM USER_TABLES
WHERE TABLE_NAME = 'EXAMPLE_TABLE';

Result:

TABLE_NAME SEGMENT_CREATED
EXAMPLE_TABLE YES

SQL> CREATE TABLE example_table (
  2      id NUMBER,
  3      name VARCHAR2(100)
  4  );

Table created.

SQL> SELECT TABLE_NAME, SEGMENT_CREATED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'EXAMPLE_TABLE';
TABLE_NAME                     SEGMENT_C
------------------------------ ---------
EXAMPLE_TABLE                  NO

SQL> INSERT INTO example_table VALUES (1, 'Alice');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT TABLE_NAME, SEGMENT_CREATED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'EXAMPLE_TABLE';
TABLE_NAME                     SEGMENT_C
------------------------------ ---------
EXAMPLE_TABLE                  YES  ★Segment was created

Pros and cons

Pros

Improved storage efficiency
No physical space is allocated until data is actually stored, avoiding waste.
Flexibility in dev/test
Even if you create many dummy or temporary tables, no storage is needed unless data is inserted.
Reduced management overhead
Storage management is simplified, and you can focus on tables that are truly in use.

Cons

Performance dip on first insert
There is overhead for creating the segment at the first insert.
Compatibility with certain features
Deferred segments do not apply to cluster tables and some special storage structures.
Harder troubleshooting
When SEGMENT_CREATED is NO, some queries or operations may not behave as expected.

Use cases and best practices

Scenario 1: Many temporary tables

In development environments that create many temporary tables, enable deferred segments to reduce storage consumption.

Scenario 2: Small environments with limited resources

When storage capacity is limited, deferred segments help run more efficiently.

Scenario 3: Production environments

In production, keep deferred segments enabled by default, and selectively disable them for specific tables when necessary.

Diagram: Deferred segment workflow

1. At table creation (deferred enabled)

CREATE TABLE test_table (id NUMBER, name VARCHAR2(100));

Segment creation: none
Storage usage: 0 bytes

2. At first insert

INSERT INTO test_table VALUES (1, 'John');

Segment creation: yes
Storage usage: data size + overhead

FAQ

Q1. When should I disable deferred segments?

When very fast first-time inserts are required and you want to avoid the initial overhead of segment creation.

Q2. Can I apply it to existing tables?

Deferred segments apply only to newly created tables. To apply to existing tables, you need to recreate them.

Summary

Deferred segments are a powerful feature for storage efficiency and reduced management effort. In certain situations, however, review the default setting. Use this article to choose the right configuration for both production and development environments.

Quick checklist:

Is DEFERRED_SEGMENT_CREATION configured appropriately?
Do you know which tables are affected by deferred segments?
Do you meet performance requirements for the first insert?

Make the most of your storage and streamline database operations!

This article targets Oracle Database 19c (other versions may differ in UI or defaults).

[Reference]
Oracle Database Database Performance Tuning Guide, 19c