Oracle DBA Lesson 45A — Measure a Table's Allocated Storage

Allocated storage answers how much space Oracle has assigned to a segment. A segment's extents provide capacity for row values, internal structures, and available space. Choose a payload measurement when the question concerns the application values themselves.

Oracle DBA Lesson 45A — Measure a Table's Allocated Storage

Read the segment total

As the owner of the course table, inspect its allocation:

SHOW USER
SHOW CON_NAME

SELECT segment_name, segment_type, tablespace_name,
       bytes, blocks, extents
FROM user_segments
WHERE segment_name = 'EMPLOYEES'
AND segment_type = 'TABLE'
AND partition_name IS NULL;

USER_SEGMENTS reports allocated storage for the current user's segments. BYTES measures allocation in bytes, BLOCKS in Oracle blocks, and EXTENTS counts the allocated extents. Retain the segment's type and tablespace with the numbers. See USER_SEGMENTS and the segment-column reference.

The owner implied by this view is the connected account. COURSE_OWNER.EMPLOYEES is the nonpartitioned heap table used here. Quoted mixed-case names require their exact stored spelling.

Explain the extent rows

SELECT extent_id, blocks, bytes
FROM user_extents
WHERE segment_name = 'EMPLOYEES'
AND segment_type = 'TABLE'
AND partition_name IS NULL
ORDER BY extent_id;

Each row represents an extent in the selected segment. EXTENT_ID identifies its allocation sequence. Application row order is established by a query's ORDER BY. BLOCKS and BYTES describe the extent's size. USER_EXTENTS scopes the report to the connected owner's segments.

Consider this allocation example, using 8 KiB Oracle blocks:

EXTENT_ID	BLOCKS	BYTES
0	8	65,536
1	8	65,536
2	8	65,536
Total	24	196,608

The arithmetic is 8 × 8,192 = 65,536 bytes per extent and 3 × 65,536 = 196,608 bytes for this segment. That is 192 KiB, or 0.1875 MiB. Read the actual block size and extent rows in your lab. Extents can have different sizes.

Reconcile the same segment identity:

SELECT COUNT(*) AS extents,
       SUM(blocks) AS blocks,
       SUM(bytes) AS bytes
FROM user_extents
WHERE segment_name = 'EMPLOYEES'
AND segment_type = 'TABLE'
AND partition_name IS NULL;

Compare the count, block sum, and byte sum with the segment report. In the example they are 3, 24, and 196608. Record the identity and time of both readings. If concurrent growth or object changes occur, repeat the reports in an instructor-designated quiet interval. An empty extent set returns a zero count and null sums. Investigate the segment state before interpreting those sums.

Add file and block placement

A segment belongs to one tablespace and can have extents in multiple data files of that tablespace. Each extent stays within one file. Its blocks are contiguous in the Oracle file address space. Storage striping can distribute those blocks physically. Oracle's storage concepts explains the relationship.

The following optional report uses an authorized administrative lab connection with delegated access to DBA_EXTENTS. COURSE_OWNER requires no extra catalog grant for the preceding owner reports. Confirm LABPDB before running this optional query:

SHOW USER
SHOW CON_NAME

SELECT extent_id, file_id, block_id, blocks, bytes
FROM dba_extents
WHERE owner = 'COURSE_OWNER'
AND segment_name = 'EMPLOYEES'
AND segment_type = 'TABLE'
AND partition_name IS NULL
ORDER BY extent_id;

FILE_ID is the absolute data-file number. BLOCK_ID is the extent's starting block. Keep both fields together. The inclusive final block is BLOCK_ID + BLOCKS - 1. DBA_EXTENTS documents these fields and the effect of offline storage on extent visibility.

For the worked map, suppose extent 0 occupies file 7, blocks 128–135; extent 1 occupies file 7, blocks 136–143; and extent 2 occupies file 9, blocks 256–263. All three belong to the example USERS tablespace. The file numbers and ranges are assumed teaching values. They do not identify files in your lab. If delegated access is unavailable, complete the owner allocation exercise and record physical placement as unverified. Use an instructor-supplied location report when one is available.

Read the allocation policy

SELECT tablespace_name, block_size, extent_management,
       allocation_type, segment_space_management
FROM user_tablespaces
WHERE tablespace_name = 'USERS';

Use the segment report's actual tablespace name. EXTENT_MANAGEMENT='LOCAL' indicates locally managed extent allocation. In that case, ALLOCATION_TYPE='SYSTEM' represents automatic extent sizing, while UNIFORM uses the configured extent size. SEGMENT_SPACE_MANAGEMENT='AUTO' means automatic segment space management (ASSM).

Tablespace extent bitmaps track allocated and available extent space. ASSM tracks free space within segments. These settings govern different levels of storage. See Oracle's tablespace management guide, USER_TABLESPACES, and the tablespace-column reference.

Keep related segments and object definitions in scope

For an ordinary heap table, each index has its own segment. LOB storage can add LOB data and associated index segments. Materialized partitions and subpartitions have their own storage identities. Group by segment name, type, and partition when extending the report beyond the single table segment.

Find index names through their relationship to the table:

SELECT index_name, index_type, partitioned, segment_created
FROM user_indexes
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name;

For a table with LOB columns, inspect their mapping separately:

SELECT column_name, segment_name, index_name, segment_created
FROM user_lobs
WHERE table_name = 'EMPLOYEES'
ORDER BY column_name;

The starter EMPLOYEES table has no LOB column, so an empty LOB list is expected under that setup. Map discovered segment names back to USER_SEGMENTS. A filter on the table's name alone omits separately named related segments. Advanced clustered and index-organized table layouts need their own mapping. The worked example uses an ordinary nonpartitioned heap table. See USER_INDEXES, ALL_INDEXES, USER_LOBS, and ALL_LOBS.

If the segment query is empty, check the table definition:

SELECT table_name, partitioned, segment_created
FROM user_tables
WHERE table_name = 'EMPLOYEES';

SEGMENT_CREATED='YES' reports a created segment. NO reports an unmaterialized table segment. An eligible empty table can defer segment creation until its first insert. N/A occurs for a partitioned logical table, whose partitions supply storage. Confirm owner, container, name spelling, and table layout when investigating an absent row. These states are documented in USER_TABLES, ALL_TABLES, and CREATE TABLE.

Practice and conditions

Use the instructor-designated Oracle 19c single-instance multitenant lab on Linux, SQL*Plus, LABPDB, and COURSE_OWNER.EMPLOYEES from the existing course setup. Record the release update, identity, and actual results. The main reports read the owner's dictionary views and require no extra catalog privilege or optional feature. No production database connection is authorized by this lesson.

1. Capture the table's segment identity, allocation, and tablespace.
2. Read its extent rows. Reconcile their count, block sum, and byte sum with the segment total.
3. Read the tablespace block size and allocation settings. Explain why your actual extent sizes may differ from the example.
4. Build a labeled extent map. Add file and block locations only from an instructor-supplied report or the separately authorized administrative connection.
5. Explain which view checks table existence and materialization if a segment row is missing. Identify related index segments where present.

Pass when the owner allocation reconciles, every mapped extent retains its segment identity, and you distinguish storage allocation from row payload. Capture a mismatch and its unresolved conditions if the readings cannot be reconciled. An authored expected result is not successful execution. The read-only exercise requires no cleanup. Additional growth experiments require a bounded instructor-owned copy and a finite quota. The shared starter table remains unchanged.

Quiz

1. Does a 1 MB segment mean 1 MB of application row data?

Yes, every allocated byte stores a column value. No, allocation also includes internal structures and available space. Yes, when the table has an index. Only when the account uses its default tablespace.

2. Three extents each contain eight 8 KiB blocks. What is their total allocation?

24 bytes 65,536 bytes 196,608 bytes 3 MiB

3. Which pair identifies the starting location of an extent in the administrative report?

FILE_ID and BLOCK_ID EXTENT_ID and the table's row count BLOCKS and the owner's default tablespace SEGMENT_TYPE and the session's temporary assignment

4. An eligible empty table appears in USER_TABLES with SEGMENT_CREATED='NO'. What explains an empty corresponding segment report?

The table has necessarily been dropped. The owner must grant itself a DBA role. Every table automatically shares an index segment. Its definition exists while segment creation is deferred.

5. Which comparison reconciles a single table segment's allocation?

Table row count against extent count SUM(USER_EXTENTS.BYTES) against USER_SEGMENTS.BYTES, using the same identity EXTENT_ID against the salary column Segment bytes against an index's name length

All displayed numbers and maps are authored examples checked against documentation. No database commands were executed while preparing this lesson. Allocation depends on the actual tablespace settings, object history, release update, and current storage state. The block-content diagram shows categories, not measured proportions.

AIOps Pro

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