Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 46 Part 2 — Why a Table Can Have No Segment

A table can exist in the data dictionary before Oracle allocates a segment for its data. This is deferred segment creation. To understand an empty table, inspect its definition, its actual rows, and its allocated storage together.

Oracle DBA Lesson 46 Part 2 — Why a Table Can Have No Segment

This lesson uses an ordinary nonpartitioned heap table in Oracle Database 19c. Allocation sizes depend on configuration.

Definition, rows and allocation

The data dictionary records a table's name, columns, and data types. A segment is the storage allocated for an object. It consists of extents containing Oracle blocks, and those blocks occupy physical data files on disk. Metadata also has storage, so deferred creation does not mean that creating the definition uses no database space at all.

Deferred creation postpones the table's data segment until its first row is inserted, unless an operation materializes the segment earlier. This saves initial space and installation work when an application creates many tables that remain unused. Allocation adds some overhead to the first insert. See deferred segment creation and logical storage structures.

For the simple table used here:

StageDefinition existsActual rows in this sessionSEGMENT_CREATEDTable segment entry
CREATE with DEFERREDYes0NOAbsent
Successful first INSERT, uncommittedYes1YESPresent
ROLLBACK of that insertYes0YESPresent

These stages show logical allocation and row state. A row moving into the segment is not a claim about physical write timing, commit durability, or a particular block count.

Where deferred creation applies

Oracle 19c's feature matrix lists deferred segment creation for Enterprise Edition and not for Standard Edition 2. The COMPATIBLE requirement is 11.2.0 or later. SEGMENT CREATION IMMEDIATE allocates the segment at CREATE instead. See feature availability by edition.

The feature does not apply to every object type. Restrictions include clustered tables, temporary tables, dictionary-managed tablespaces, and SYSTEM. A partitioned table can report N/A at table level. Inspect the appropriate partition views. Serializable transactions have a documented restriction when inserting into a table whose segment has not been created. This example uses READ COMMITTED and a table with no indexes, LOBs, or partitions. See CREATE TABLE.

Create the definition explicitly

DEFERRED selects allocation timing for this table. It overrides the DEFERRED_SEGMENT_CREATION initialization parameter. That parameter defaults to TRUE in the reference documentation, but it can be changed. Do not infer every empty table's allocation state from the default. See DEFERRED_SEGMENT_CREATION.

CREATE TABLE defer_demo (id NUMBER)
  SEGMENT CREATION DEFERRED;

Inspect both views as the owner, in the same container:

SELECT table_name, segment_created
FROM user_tables
WHERE table_name = 'DEFER_DEMO';

SELECT segment_name, bytes
FROM user_segments
WHERE segment_name = 'DEFER_DEMO';

Expected table metadata:

TABLE_NAMESEGMENT_CREATED
DEFER_DEMONO

The segment query returns no rows at this stage. USER_TABLES describes your tables, including this definition. USER_SEGMENTS describes storage allocated for your objects. An absent segment entry does not establish that CREATE failed. Check the table metadata, the exact name and case, the owner, and the container before concluding that an object is missing. See USER_TABLES, ALL_TABLES, USER_SEGMENTS, and DBA_SEGMENTS.

First insert and rollback

INSERT INTO defer_demo (id) VALUES (1);
-- No COMMIT here.
ROLLBACK;

The first successful insert materializes the table segment before commit. The inserting session can see its uncommitted row. ROLLBACK removes that row, but the allocated segment remains. Rolling back the row change does not restore the earlier unallocated state. A SELECT between these statements does not commit the insert. DDL can implicitly commit, so ordinary DDL between the insert and ROLLBACK changes the transaction sequence. See ROLLBACK.

After rollback, inspect the actual rows and repeat both metadata queries:

SELECT COUNT(*) AS rows_now
FROM defer_demo;
ROWS_NOW
0
TABLE_NAMESEGMENT_CREATED
DEFER_DEMOYES

One possible segment result, the size used in the video:

SEGMENT_NAMEBYTES
DEFER_DEMO65536

65536 is an illustrative allocation, not a promised initial size and not the bytes occupied by row 1. BYTES measures allocated segment size. Zero rows, YES, and a positive allocated byte count can all be correct at the same time.

When IMMEDIATE helps

For a controlled comparison, use a second unused name:

CREATE TABLE ready_demo (id NUMBER)
  SEGMENT CREATION IMMEDIATE;

This table starts with no rows and an allocated data segment. IMMEDIATE helps when an application or an inspection needs a segment entry immediately after CREATE. It incurs the initial allocation for tables that may remain empty. Before either table's first insert, both have zero rows. Their allocation timing differs.

CREATE success with DEFERRED does not prove that later allocation will succeed. A first insert can fail when the owner's usable quota or the tablespace space is insufficient. Distinguish the reported quota or capacity error, verify the target tablespace, and investigate that condition. Do not treat unlimited quota or a larger data file as the automatic fix. Quota is a limit, not a reservation of physical space. See ALTER USER and quotas.

ALLOCATE EXTENT on the table can materialize the segment earlier. In the workflow shown here, the first insert is the allocation trigger. It is not the only possible trigger. Deferred behavior can also cover eligible dependent index and LOB segments. Inspect their own metadata when they are relevant.

Recap

A table definition can exist before Oracle allocates its data segment. SEGMENT CREATION DEFERRED postpones that segment until the first successful insert, which allocates it before commit. ROLLBACK removes the row and leaves the segment in place. The table can then show zero rows, SEGMENT_CREATED = YES, and a positive USER_SEGMENTS.BYTES value together. An absent segment row does not mean CREATE failed. Check USER_TABLES, the name and case, the owner, and the container.

DEFERRED overrides DEFERRED_SEGMENT_CREATION, whose documented default is TRUE. IMMEDIATE allocates at CREATE. Deferred creation requires COMPATIBLE 11.2.0 or later, and the 19c feature matrix lists it for Enterprise Edition and not for Standard Edition 2. It does not apply to clustered or temporary tables, dictionary-managed tablespaces, or SYSTEM. A partitioned table can report N/A at table level. 65536 is one illustrative allocation, not a promised size. A later first insert can still fail when quota or tablespace space is insufficient. ALLOCATE EXTENT can materialize a segment earlier, and eligible index and LOB segments can be deferred as well.

Quiz

1. Does an absent USER_SEGMENTS entry prove that CREATE TABLE failed?

2. In this DEFERRED workflow, when is the data segment first allocated?

3. What remains after the first insert is rolled back?

4. Why might a first insert fail even after deferred CREATE succeeded?

5. What does a positive USER_SEGMENTS.BYTES value establish for this example?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...