Oracle DBA Lesson 43A — Find Which Storage Layer Is Full

A storage alert becomes useful when you can connect the affected table to its allocated segment, its tablespace, and the files that contain its extents. Then compare the space available for allocation with the files' growth settings and the capacity of their storage.

Oracle DBA Lesson 43A — Find Which Storage Layer Is Full

From a table to blocks and files

A tablespace is a logical container for segments. A segment collects the extents allocated to one storage structure, such as a heap table or an index. An extent contains logically contiguous Oracle blocks in one data file. The blocks hold data and the information Oracle uses to manage that data.

Each segment belongs to one tablespace. In a smallfile tablespace, different extents of the same segment can occupy different data files. Each individual extent stays within one file. Physical storage can stripe or mirror file contents across devices. A bigfile tablespace has one data file.

See logical storage structures for these relationships, and the tablespace administration guide for smallfile and bigfile tablespaces.

A table can have several associated storage structures: partitions, indexes, and large objects (LOBs). Identify the individual segments and their assignments when you investigate such an object. A simple, allocated, nonpartitioned heap table keeps this first trace easy to follow.

Start in the intended container

The examples assume Oracle 19c, a single-instance lab, an open LABPDB, and a nonpartitioned COURSE_OWNER.EMPLOYEES heap table with an allocated segment. The instructor supplies a local COURSE_DBA account with read access to the dictionary views used below. That account name does not automatically confer the DBA role.

Run the checks from your approved lab connection:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS database_name,
       SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

SHOW is a SQL*Plus command. SELECT reads database information. Confirm the expected user, database, and LABPDB before interpreting object names or file numbers.

When working from the root, use the appropriate CDB_ views with delegated access and container visibility, and retain CON_ID in the report and joins. A file number or tablespace name by itself can lose its container context. See CDB views.

Find the segment and its tablespace

SELECT segment_name, segment_type, tablespace_name,
       bytes, blocks, extents
FROM dba_segments
WHERE owner = 'COURSE_OWNER'
  AND segment_name = 'EMPLOYEES'
  AND segment_type = 'TABLE';

An assumed example row is:

Segment	Type	Tablespace	Bytes	Blocks	Extents
EMPLOYEES	TABLE	USERS	131072	16	2

131072 / 1024 = 128 KiB is the allocated segment size. With an assumed 8 KiB block size, 16 blocks also occupy 128 KiB. This includes management overhead and space that may be available for reuse within the segment. Live row data is only part of that allocation. See DBA_SEGMENTS.

A table definition can exist before its segment is allocated. For an empty object using deferred segment creation, the segment and extent queries may return no rows. Confirm its definition and allocation state with the relevant table or partition metadata, such as DBA_TABLES.SEGMENT_CREATED. Also check the owner, container, privileges, and availability. Record an unallocated segment as unallocated. A file relationship needs actual extent rows. See segment creation for deferred allocation.

Trace its extents to the files they occupy

SELECT e.extent_id, e.file_id, e.block_id, e.blocks,
       e.bytes, e.tablespace_name, f.file_name
FROM dba_extents e
JOIN dba_data_files f
  ON f.file_id = e.file_id
 AND f.tablespace_name = e.tablespace_name
WHERE e.owner = 'COURSE_OWNER'
  AND e.segment_name = 'EMPLOYEES'
  AND e.segment_type = 'TABLE'
ORDER BY e.extent_id;

DBA_EXTENTS.FILE_ID identifies the data file containing that extent. Joining it to DBA_DATA_FILES.FILE_ID supplies the filename. The tablespace condition makes the relationship clear within the selected container.

The video uses these assumed rows:

Extent	File ID	Blocks	Bytes	Tablespace	File
0	7	8	65536	USERS	users01.dbf
1	8	8	65536	USERS	users02.dbf

Each extent occupies 64 KiB. Together they account for the segment's 128 KiB. The actual query returns full filenames and starting block numbers from your lab. Preserve those values in your observation.

See DBA_EXTENTS for the extent and file relationship. Listing all data files assigned to USERS gives that tablespace's inventory. The extent rows tell you which of those files contain this particular segment. For a partitioned table, inspect each partition segment. Use the appropriate index or LOB dictionary metadata to identify associated segment names.

Compare allocation, file growth, and storage capacity

First inspect the tablespace's identity and availability:

SELECT tablespace_name, contents, status, block_size, bigfile
FROM dba_tablespaces
WHERE tablespace_name = 'USERS';

CONTENTS identifies permanent, temporary, or undo storage. STATUS reports availability. BLOCK_SIZE gives the unit used for block counts. BIGFILE distinguishes the single-file design. See DBA_TABLESPACES.

Then inspect every data file in that tablespace, including free extents:

SELECT f.file_id, f.file_name, f.online_status,
       f.bytes, f.user_bytes, f.autoextensible,
       f.maxbytes, f.increment_by,
       NVL(s.free_bytes, 0) AS free_bytes,
       NVL(s.largest_free_extent, 0) AS largest_free_extent
FROM dba_data_files f
LEFT JOIN (
  SELECT file_id, SUM(bytes) AS free_bytes,
         MAX(bytes) AS largest_free_extent
  FROM dba_free_space
  WHERE tablespace_name = 'USERS'
  GROUP BY file_id
) s ON s.file_id = f.file_id
WHERE f.tablespace_name = 'USERS'
ORDER BY f.file_id;

Interpret the columns together:

• BYTES is the current file size. USER_BYTES excludes file metadata space.
• FREE_BYTES and the largest free extent describe space available for new allocation within the current files.
• AUTOEXTENSIBLE=YES permits automatic growth up to the configured maximum. Examine MAXBYTES together with INCREMENT_BY, whose unit is Oracle blocks.
• A growth increment also needs capacity on the storage holding that file.

The left join keeps a file visible when it has no free-extent row. See DBA_FREE_SPACE. A file with no free space has no row. The zero produced by NVL needs availability checks: offline files and tablespaces can also have missing extent information. Diagnose from an online, accessible inventory. Resolve missing visibility before treating it as a measured capacity state.

See DBA_DATA_FILES for current size, usable size, and growth columns, and data-file administration for automatic extension.

Example 1: free filesystem, fixed full files

Assume both USERS files are online and accessible, each has a current size of 100 MiB, the report shows zero usable free allocation space, and both have AUTOEXTENSIBLE=NO. The filesystem has 2 GiB available. Oracle still cannot allocate the next extent in those existing files: they have no free allocation space and their sizes remain fixed.

The error ORA-01653 indicates that the table segment could not receive the requested extent in the named tablespace. Read the exact alert and investigate the relevant layer before proposing a space change. See ORA-01653. This lesson diagnoses the constraint. Changing files or tablespaces is a separate approved operation.

Example 2: growth permitted, underlying storage full

Assume a file has autoextend enabled, remains below its maximum, and needs another growth increment. If its underlying filesystem has no capacity for that increment, the extension can fail. Compare the filename with the storage that actually holds it. Free space on an unrelated mount does not help this file grow.

For filesystem files, the instructor supplies an approved Linux capacity report for the actual lab path, for example df -h /approved/lab/path. Replace that placeholder only with the instructor-designated path. For ASM files, use the authorized ASM disk-group capacity report. Account for redundancy and usable capacity through its own tools. Linux filesystem free space is a separate storage measure.

All rows, sizes, and alert circumstances above are authored teaching examples. They are not observations from a connected database. Your lab may use different file numbers, extent counts, and paths.

Recognize the other file roles

File	Job
Control file	Tracks database structure, file locations, and recovery metadata
Online redo log	Records database changes
Archived redo log	Preserves a filled redo log member for recovery history
Tempfile	Supplies temporary workspace, for example a sort that spills to disk
Password file	Supports administrative authentication
Optional block change tracking file	Identifies changed data blocks to help incremental backup scans

Query temporary-file inventory separately:

SELECT file_id, tablespace_name, file_name, bytes,
       autoextensible, maxbytes
FROM dba_temp_files
ORDER BY tablespace_name, file_id;

See DBA_TEMP_FILES for that inventory. File roles are described in physical storage, administrator authentication, and block change tracking. Change tracking is optional and disabled by default. Its use must fit the instructor's edition and feature eligibility. No enabling or disabling is part of this exercise.

Read-only practice

Use the designated disposable lab. The instructor provisions COURSE_DBA with access to DBA_TABLESPACES, DBA_SEGMENTS, DBA_EXTENTS, DBA_DATA_FILES, DBA_FREE_SPACE, DBA_TEMP_FILES, and applicable table metadata. An open and accessible LABPDB and an allocated COURSE_OWNER.EMPLOYEES segment are needed for the demonstrated file trace. Encrypted tablespaces may require the instructor to arrange an open keystore before dictionary access. Do not change its state as part of this exercise.

1. Confirm your database, user, and container.
2. Find the table segment and record its tablespace and allocated bytes.
3. Trace its extent rows to file IDs and actual filenames.
4. Inspect all files in the tablespace, their available allocation space, growth settings, and availability.
5. Compare them with the instructor's matching filesystem or ASM capacity report.
6. Explain the fixed-full-files example and the autoextend-on-full-storage example in your own words.

You pass when the trace follows real metadata and your explanation identifies which layer limits the next allocation. If the segment is deferred or the views are inaccessible, record that condition and ask the instructor for the required lab evidence. Do not invent rows to finish the trace. These queries read metadata. No database changes or cleanup are required. Actual lab execution remains pending until you perform it in the approved environment.

Quiz

1. Can one segment's extents span two tablespaces?

Yes, whenever a table has an index. No; each individual segment has one tablespace. Yes, if it has multiple extents. Only when autoextend is enabled.

2. Which metadata identifies the files actually occupied by a table segment?

Every filename in the database. The tablespace name alone. The segment's extent rows, joined to file metadata using FILE_ID. The number of live rows.

3. What can DBA_SEGMENTS.BYTES tell you?

The segment's allocated storage size. The exact total of its live row values. Free capacity on the filesystem. The password-file size.

4. Why can a tablespace allocation fail while its filesystem has free capacity?

Filesystem free capacity always forces a new extent. Every table must use a different filesystem. The password file contains the table rows. The current files have no allocatable space and cannot grow under their present settings.

5. An autoextensible file is below its maximum, but the next extension needs storage that is unavailable. What should you conclude?

AUTOEXTENSIBLE=YES guarantees success. The next extension can fail because it also needs underlying storage capacity. A control file holds the missing extent. File capacity can be inferred from a table's row count.

AIOps Pro

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