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:
| Stage | Definition exists | Actual rows in this session | SEGMENT_CREATED | Table segment entry |
|---|---|---|---|---|
| CREATE with DEFERRED | Yes | 0 | NO | Absent |
| Successful first INSERT, uncommitted | Yes | 1 | YES | Present |
| ROLLBACK of that insert | Yes | 0 | YES | Present |
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_NAME | SEGMENT_CREATED |
|---|---|
| DEFER_DEMO | NO |
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_NAME | SEGMENT_CREATED |
|---|---|
| DEFER_DEMO | YES |
One possible segment result, the size used in the video:
| SEGMENT_NAME | BYTES |
|---|---|
| DEFER_DEMO | 65536 |
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.
No comments:
Post a Comment