Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 45 Part 1 — Measure a Segment’s Allocation

A segment's allocation tells you how much database storage belongs to that segment. It includes row data, internal structures, and available space within the allocation. It does not measure only the values currently stored in application rows.

Oracle DBA Lesson 45 Part 1 — Measure a Segment’s Allocation

Identify a segment, inspect its extents, and reconcile the allocation totals. Part 2 examines deleting rows, reuse, the high water mark, and physical file size.

Segment, extent and block

A segment is a set of extents allocated for a storage structure such as an ordinary table or an index. An extent consists of consecutive Oracle blocks within one data file. A segment belongs to one tablespace. Its extents may be in different data files of that tablespace. A single extent cannot cross data files. These allocations are backed by physical data files on disk. Database blocks need not correspond one-for-one with operating-system blocks or with physically adjacent sectors.

The table object and its allocated segment are related but distinct. A table created with deferred segment creation can appear in USER_TABLES before there is a corresponding table segment in USER_SEGMENTS.

See logical storage structures.

Identify and measure the table segment

USER_SEGMENTS lists segments owned by the current user in the current container. For an ordinary unquoted name, Oracle stores the name in uppercase.

SELECT segment_name, segment_type, tablespace_name,
       bytes, blocks, extents
FROM user_segments
WHERE segment_name = 'ORDERS'
  AND segment_type = 'TABLE';
SEGMENT_NAMESEGMENT_TYPETABLESPACE_NAMEBYTESBLOCKSEXTENTS
ORDERSTABLEAPP_DATA1,048,5761282
  • SEGMENT_TYPE identifies the storage structure being measured. The predicate here selects the ordinary table segment.
  • TABLESPACE_NAME locates its logical storage container.
  • BYTES is the segment's allocated size in bytes.
  • BLOCKS is the allocated size in Oracle blocks.
  • EXTENTS is the number of extents allocated to the segment.

With an 8 KiB block size:

1 KiB = 1,024 bytes
1 MiB = 1,048,576 bytes
128 blocks × 8,192 bytes/block = 1,048,576 bytes = 1 MiB

Use the actual tablespace block size. A tablespace can have a nondefault block size, so do not automatically substitute DB_BLOCK_SIZE for every tablespace. A small current row count does not determine the extent count. An initial allocation can be requested as 1 MiB, or the allocation can reflect previous growth.

SELECT tablespace_name, block_size,
       extent_management, allocation_type,
       segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

For APP_DATA, BLOCK_SIZE is 8192, EXTENT_MANAGEMENT is LOCAL, ALLOCATION_TYPE is UNIFORM, and SEGMENT_SPACE_MANAGEMENT is AUTO. See USER_SEGMENTS, DBA_SEGMENTS, and DBA_TABLESPACES.

Inspect each allocated extent

SELECT extent_id, blocks, bytes
FROM user_extents
WHERE segment_name = 'ORDERS'
  AND segment_type = 'TABLE'
ORDER BY extent_id;
EXTENT_IDBLOCKSBYTES
064524,288
164524,288

Each row describes one currently allocated extent. EXTENT_ID is the extent number within the segment. It is not an ordering of application rows, a primary key for the table, or a global file number. Table rows have no guaranteed result order without an appropriate ORDER BY in their own query.

Each extent has 64 blocks and 524,288 bytes. At 8 KiB per block, that is 512 KiB, the uniform extent size for APP_DATA.

The query above uses the allocation columns from USER_EXTENTS. When the physical location is needed, include the owner and read DBA_EXTENTS:

SELECT extent_id, file_id, block_id, blocks, bytes
FROM dba_extents
WHERE owner = 'APP_USER'
  AND segment_name = 'ORDERS'
  AND segment_type = 'TABLE'
ORDER BY extent_id;

FILE_ID identifies the containing data file. BLOCK_ID is the starting block number within that file. An extent of 64 blocks starting at block 128 covers block numbers 128 through 191 in that file. Join FILE_ID to DBA_DATA_FILES.FILE_ID to identify the file name in the current container. Logical file offsets do not establish physical disk-sector adjacency. Across containers, retain the container identity as well as the local object and file identifiers. See USER_EXTENTS and DBA_EXTENTS.

Reconcile the totals

SELECT COUNT(*) AS extent_count,
       SUM(blocks) AS total_blocks,
       SUM(bytes) AS total_bytes
FROM user_extents
WHERE segment_name = 'ORDERS'
  AND segment_type = 'TABLE';
EXTENT_COUNTTOTAL_BLOCKSTOTAL_BYTES
21281,048,576

The extent count matches USER_SEGMENTS.EXTENTS. The sums match its BLOCKS and BYTES:

2 × 64 blocks = 128 blocks
2 × 524,288 bytes = 1,048,576 bytes

Compare the same owner, container, segment type, and partition identity. Concurrent allocations or other storage changes between separate queries can alter the result. If there are no matching extent rows, COUNT(*) returns zero while SUM returns NULL. That result is not a measured zero-byte segment. Check the object, the filters, and the environment before drawing a conclusion. See Managing Space for Schema Objects.

What the allocation cannot tell you

The 1 MiB total includes block and segment management structures, row storage, and space available within the segment. Those dictionary totals do not establish the percentage occupied by row values or by internal metadata. They do not measure the exact row payload, or the amount a storage-reclamation operation could return.

COUNT(*) on the application table measures rows visible to that query. NUM_ROWS and AVG_ROW_LEN in table statistics may be stale, and they are estimates for this purpose. Multiplying them does not produce an exact measurement of segment occupancy. Detailed block-space inspection may require an appropriate DBMS_SPACE procedure and a supported object type. Choose that investigation for the quantity actually needed.

Extent size and space management

In a locally managed tablespace, bitmaps track allocated and free extents. With UNIFORM, every extent has the configured uniform size. With AUTOALLOCATE, Oracle chooses extent sizes, and those sizes can differ. Extrapolate a whole segment from one extent row only when the actual allocation supports that reading.

Automatic segment space management (ASSM, shown as AUTO in DBA_TABLESPACES.SEGMENT_SPACE_MANAGEMENT) manages available block space within the segment. Extent allocation and block-space availability are distinct mechanisms.

Include the relevant segments

An ordinary table's indexes have their own segments. Large-object storage, and table or index partitions or subpartitions, can introduce additional segments. A logical partitioned table may not have one ordinary TABLE segment covering all of its data. Inspect the actual segment types and partition names before adding them. Similar names can be different segments, and the same allocation belongs in the total only once.

For an overall table-storage total, identify the table's own segment allocations, its index allocations, and the applicable large-object and partition allocations. A single USER_SEGMENTS row for ORDERS represents only that row's segment.

Empty table with no segment yet

SELECT table_name, segment_created
FROM user_tables
WHERE table_name = 'ORDERS_NEW';
TABLE_NAMESEGMENT_CREATED
ORDERS_NEWNO

For this eligible ordinary heap table, NO confirms that its table segment has not been created. Deferred segment creation can postpone allocation until the first insert. Configuration, object type, and storage conditions matter. An empty table can also already have a segment. SEGMENT_CREATED = 'N/A' applies to a logical partitioned table. It does not prove that a segment is missing. For partitions, inspect the appropriate partition views and the actual segment rows.

If a query returns no object, also check the current user and container, the quoted-name case, and the predicate. A missing segment row alone does not prove that the table is absent. Offline files or tablespaces can affect extent-view visibility. See USER_TABLES, ALL_TABLES, and deferred segment creation.

Recap

Read allocated bytes, Oracle blocks, and the extent count from USER_SEGMENTS, using the tablespace block size. Sum the matching USER_EXTENTS rows and compare the count, blocks, and bytes for the same owner, container, segment type, and partition identity. The total includes internal structures and available space, so it is not the current row payload. Measure indexes, large objects, and partitions as their own segments. SEGMENT_CREATED = 'NO' on an eligible ordinary heap table means the table exists and its table segment has not been created.

Quiz

1. Does a 1 MiB segment prove that current application row values occupy 1 MiB?

2. Two extents each contain 64 blocks in an 8 KiB tablespace. What is their total allocation?

3. What does EXTENT_ID identify?

4. ORDERS_NEW appears in USER_TABLES with SEGMENT_CREATED = 'NO'. What follows for an eligible ordinary heap table?

5. The ORDERS table-segment row reports 1 MiB. How should its index allocation be treated in an overall table-storage calculation?

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 ...