Oracle DBA Lesson 46B — When Does a Table Get Storage?

Oracle records a table's definition in the data dictionary. A table segment is the allocated storage for that table's data. With deferred segment creation, an eligible table's definition can exist before its data segment is allocated. This saves the initial table allocation for objects that remain unused. The dictionary itself still uses database resources.

Oracle DBA Lesson 46B — When Does a Table Get Storage?

The useful question is: does the table exist, and has its data segment been created? Read the table metadata, segment metadata, and row count together. The owner and current container determine the scope of these observations.

Read the three observations

For a simple, nonpartitioned table owned by the connected user:

Observation	What it tells you
Matching USER_TABLES row	The table definition exists in this user's schema.
USER_TABLES.SEGMENT_CREATED	NO means the table segment has not been created. YES means it has.
Matching USER_SEGMENTS row	Storage is allocated for this segment. BYTES describes allocated size.
COUNT(*) against the table	Rows visible to this statement in this session.

USER_SEGMENTS supplies the same allocation perspective used in the previous storage lesson. Here, its purpose is to identify when the first allocation occurs. An absent row should lead you to inspect the table definition and its segment state. For partitioned tables, the logical table can have SEGMENT_CREATED = 'N/A'. Inspect the relevant partition views instead. The example below deliberately uses a nonpartitioned heap table. See table-state reference, owner table view, USER_SEGMENTS, and segment allocation reference.

Follow creation, first insert and rollback

Assume an entitled Oracle 19c edition, an eligible heap table, sufficient quota and space, the correct owner and container, and an otherwise unused table name. This is a documented teaching example, not output from a connected database. The state table is hypothetical under these conditions. Record all actual allocation amounts and results in your lab. The storage diagrams are schematic: their box counts and sizes predict no actual extent or block count. No guaranteed byte value is assigned.

CREATE TABLE course_deferred_demo (id NUMBER)
  SEGMENT CREATION DEFERRED;

The explicit clause selects deferred allocation for this table, overriding the relevant default. See DEFERRED_SEGMENT_CREATION. The definition is created now. Its data segment is materialized when the first row is inserted. Query the two metadata layers:

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

SELECT segment_name, segment_type, bytes, blocks
FROM user_segments
WHERE segment_name = 'COURSE_DEFERRED_DEMO'
  AND segment_type = 'TABLE';

SELECT COUNT(*) AS visible_rows
FROM course_deferred_demo;

At this initial step, the stated deferred example has a table row with SEGMENT_CREATED = 'NO', zero data rows, and no matching table segment row. The missing segment row represents pending allocation.

Insert one row and inspect the same observations before any commit or DDL:

INSERT INTO course_deferred_demo (id) VALUES (1);

The first insert allocates the segment. This session can see its uncommitted row. SEGMENT_CREATED becomes YES. USER_SEGMENTS reports the allocation. The bytes depend on storage and tablespace settings.

Now undo the insert:

ROLLBACK;

Repeat the three observation queries. The row count returns to zero, while the created segment is retained. Oracle documents that these segments are materialized even when the initial insert is uncommitted or rolled back. See CREATE TABLE: deferred segment creation and ROLLBACK.

Example step	Table definition	Rows visible in this session	SEGMENT_CREATED	Matching table segment
Successful CREATE ... DEFERRED	Exists	0	NO	Absent
Successful first INSERT, before commit	Exists	1, uncommitted	YES	Allocated
ROLLBACK of that insert	Exists	0	YES	Retained

Row rollback and segment allocation have different consequences. The zero-row table after rollback can use its allocated segment for later rows. To repeat the initial deferred state, clean up and recreate this exact disposable table after the required checks. Do not expect another rollback to restore the pre-allocation state.

Choose the allocation event

Choice	Initial allocation event	Useful consequence
SEGMENT CREATION DEFERRED	First insert	Avoids initial data-segment allocation for an unused eligible table.
SEGMENT CREATION IMMEDIATE	CREATE TABLE	Provides an allocated segment from the start, including an entry in the segment views.

An application that depends on segment-view visibility at installation can use IMMEDIATE. With DEFERRED, quota or tablespace capacity problems can surface when the first insert requires allocation. A successful CREATE therefore leaves a future allocation event to plan for. Both choices still require capacity for subsequent growth. A user quota is an allocation allowance. It reserves no physical space. Check available quota and actual tablespace and file capacity separately. Oracle explains the capacity planning and first-insert work in Managing Tables, section 20.2.14.

Conditions for the disposable practice

Use only an instructor-provided disposable Oracle 19c lab: COURSE_OWNER in LABPDB, a permanent writable USERS tablespace, CREATE SESSION, CREATE TABLE, and positive finite available quota. Record the database identity, release and RU, edition, feature entitlement, storage conditions, and reset plan before mutation. The course's ordinary setup provides a finite quota. Confirm its remaining amount for this exercise.

Edition and feature: Oracle 19c's Licensing Information, table 1-13 lists Deferred Segment Creation as N for on-premises SE2 and Y for on-premises EE/EE-ES. The cloud columns have their own offering-specific values. Check the actual offering and entitlement. Installed capabilities or V$OPTION values do not establish a license. If the designated lab lacks the feature, complete the documented-state comparison conceptually, or use the explicitly permitted IMMEDIATE comparison and record the deferred execution as unavailable. Do not claim the deferred sequence was executed.

Object and storage: Use a regular nonpartitioned heap table with the single NUMBER column shown, in a locally managed permanent tablespace outside SYSTEM. Compatibility must be at least 11.2.0. This exercise excludes clustered, temporary, internal, and external tables and Oracle-maintained owners. See the restrictions in CREATE TABLE. Keep normal read-committed transaction behavior. First insertion into a segmentless table is unsupported in a serializable transaction. Have the instructor verify session and client transaction settings. Do not run a serializable test or alter any database-wide settings to fit the demonstration.

Ownership and names: Both COURSE_DEFERRED_DEMO and the optional COURSE_IMMEDIATE_DEMO must be unused. Check USER_OBJECTS, not just USER_TABLES, before creating either. Stop if any matching object exists. Do not overwrite or drop someone else's work. Do not modify the shared starter tables or schema defaults. Record exactly which scratch objects this run creates.

Transactions and reset: Start a fresh dedicated session with no unrelated pending work. Ordinary CREATE TABLE and DROP TABLE are DDL: Oracle commits before a syntactically valid DDL statement, even one that errors, and after successful DDL. A rollback cannot undo the creation or purge. Keep the uncommitted insert and its observation queries together. Avoid autocommit, DDL, or client exit between the insert and rollback. Explicitly roll back the scratch insert before cleanup. An instructor-owned reset copy is the fallback if exact cleanup cannot be established. See COMMIT.

Read-only preflight

Connect through the instructor-provided SQL*Plus service using its password prompt or approved wallet. These checks are SQL*Plus commands followed by SQL. They do not authorize opening a live production connection.

SHOW USER
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name,
       SYS_CONTEXT('USERENV','CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV','SESSION_USER') AS session_user
FROM dual;

SELECT object_name, object_type
FROM user_objects
WHERE object_name IN
  ('COURSE_DEFERRED_DEMO', 'COURSE_IMMEDIATE_DEMO');

SELECT privilege
FROM session_privs
WHERE privilege IN ('CREATE TABLE', 'UNLIMITED TABLESPACE');

SELECT username, default_tablespace
FROM user_users;

SELECT tablespace_name, bytes, max_bytes,
       max_bytes - bytes AS remaining_quota_bytes
FROM user_ts_quotas
WHERE tablespace_name = 'USERS';

Require COURSE_OWNER in the intended LABPDB, no matching scratch objects, CREATE TABLE, default permanent tablespace USERS, and a positive remaining finite quota (MAX_BYTES > 0 and MAX_BYTES > BYTES). MAX_BYTES = -1 denotes unlimited quota, which falls outside this finite-quota exercise. UNLIMITED TABLESPACE would override the intended quota boundary. Ask the instructor for the proper lab identity instead of changing privileges yourself.

The instructor's separately authorized account should verify USERS is permanent, writable, locally managed, and has sufficient available extents and file capacity for both scratch objects if comparing them. It should also confirm the release and RU, compatibility, entitled edition, and intended container. Account quota and tablespace free space measure separate limits. Allow allocation granularity and headroom. If your account lacks the relevant administrative views, have the instructor supply the evidence rather than granting broad privileges. The owner views are described in USER_USERS, USER_TS_QUOTAS, and DBA_TS_QUOTAS. Tablespace properties are described in DBA_TABLESPACES.

Independent practice and cleanup

1. Complete the preflight and write down the exact scratch names and conditions. Stop on any mismatch or unavailable feature.
2. Create only COURSE_DEFERRED_DEMO using the statement above. Record table existence, SEGMENT_CREATED, visible row count, and the matching segment query. Save actual values and errors. Do not copy the example table as evidence.
3. Insert ID = 1. In the same session, run the three observation queries while it is uncommitted. Record allocation bytes from your own result.
4. Run ROLLBACK explicitly, repeat the observations, and explain the zero-row table with a retained segment. If an insert errors, stop, record the error, roll back any scratch transaction, and inspect the resulting state before deciding how to clean up. Never label a failed or missing step as a successful allocation.
5. If the comparison is useful and separately permitted, create the already checked unused name with CREATE TABLE course_immediate_demo (id NUMBER) SEGMENT CREATION IMMEDIATE;. Apply the same observation queries with that exact name. Its successful creation should provide allocated storage while its row count is zero. This comparison leaves all defaults unchanged.
6. Reconfirm the account and container and your record of which objects this run created. Clean up only those confirmed objects, one statement at a time:

   ROLLBACK;
   DROP TABLE course_deferred_demo PURGE;
   -- Only if this run created the optional comparison:
   DROP TABLE course_immediate_demo PURGE;

   PURGE bypasses recycle-bin recovery for these disposable tables and releases their allocation to the tablespace. It is irreversible through ordinary rollback. Do not run the optional drop when that object was not created by this run. See DROP TABLE.
7. Repeat the exact USER_OBJECTS, USER_TABLES, and USER_SEGMENTS name checks. Require no matching scratch rows, unchanged starter tables, and unchanged defaults. If cleanup fails or ownership is uncertain, preserve evidence and use the instructor's disposable reset procedure. Do not broaden the purge or alter capacity settings.

Pass criterion: explain each observed state, why the segment remains after first-insert rollback, which event needs allocation capacity, and which edition and feature conditions enabled the deferred test. Complete exact cleanup. If deferred execution is unavailable, state the limitation and complete the conceptual comparison without claiming lab success.

Quiz

1. USER_TABLES contains an eligible empty heap table with SEGMENT_CREATED = 'NO'. Its table segment query has no row. What is the appropriate interpretation?

Table creation necessarily failed The table definition exists and its data segment is deferred The table already has allocated data blocks Every empty table in every edition has the same state

2. A successful first insert into the example table is rolled back without intervening commit or DDL. Which state follows?

One durable row and no segment The table definition disappears Zero rows with the created segment retained Guaranteed zero allocated bytes

3. When does SEGMENT CREATION IMMEDIATE allocate the initial table segment?

During CREATE TABLE Only at the first commit Only when querying USER_SEGMENTS Only after a rollback

4. The 19c licensing matrix lists deferred segment creation as unavailable for your on-premises SE2 lab. What should you do?

Change a database parameter until it works Treat an installed option as entitlement Report the conceptual comparison as executed Use the documented conceptual alternative or an authorized immediate comparison

5. Why check quota and tablespace capacity before the first insert into a deferred table?

Querying metadata consumes the entire quota The first insert requires initial segment allocation, and a quota reserves no physical capacity Rollback always purges all table storage Successful creation guarantees capacity for all later rows

The state table and storage diagrams in this lesson are teaching examples, not output from a connected database. Run the practice in the designated Oracle Database 19c lab and record the allocation amounts and results you actually observe.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro