An allocation failure can occur while the filesystem still has room. Oracle must find or create space for the required extent inside the owning tablespace. Diagnose the container, segment, extent, data file, and storage capacity together.
Oracle DBA Lesson 43 Part 1 — Find Which Storage Layer Is Full
Storage relationships
| Structure | Purpose and boundary |
|---|---|
| Tablespace | Logical storage container within the connected database container. A permanent tablespace contains segments backed by data files. |
| Segment | Allocated storage for a particular structure, such as an ordinary table or index. Each individual segment belongs to exactly one tablespace. |
| Extent | A group of logically consecutive Oracle data blocks allocated to a segment. An extent stays within one data file. Different extents of the same segment can occupy different files of its tablespace. |
| Oracle data block | Oracle's basic logical unit for managing stored data. Block locations here are positions in persistent data files on disk. Storage hardware and filesystem striping determine the underlying physical placement. |
| Data file | A physical file backing one tablespace. A smallfile tablespace can have several data files. A bigfile tablespace has one large data file. |
An ordinary nonpartitioned heap table has a table segment once storage is allocated. Its indexes have separate segments. Table and index partitions, and LOB storage, can introduce additional segments, potentially in other tablespaces. Trace every segment relevant to the failing operation. Do not assume a complex object's storage has one location.
Allocated segment bytes include block overhead and unused space inside allocated blocks. They measure allocated storage, rather than the sum of live row payloads. A table with deferred segment creation may have no segment until the first insert allocates storage. Record that condition instead of inventing an extent or a filename.
See logical storage structures.
Check the intended container
SHOW CON_NAME
SHOW USER
SHOW is a SQL*Plus command. Confirm the returned container and identity before interpreting dictionary rows. The following queries use DBA_* views from that pluggable database. Root-level CDB_* analysis needs the relevant CON_ID, the correct privileges, and container visibility. Do not combine rows from different containers by owner and name alone.
Find the tablespace and segment
SELECT tablespace_name, contents, status, bigfile
FROM dba_tablespaces
WHERE tablespace_name='APP_DATA';
| TABLESPACE_NAME | CONTENTS | STATUS | BIGFILE |
|---|---|---|---|
| APP_DATA | PERMANENT | ONLINE | NO |
CONTENTS distinguishes permanent, temporary, and undo storage. BIGFILE=NO means this is a smallfile tablespace. STATUS describes the tablespace's accessibility. Verify individual files as well before interpreting missing free-space rows. APP_DATA is locally managed, and both of its files are online. See DBA_TABLESPACES.
SELECT segment_name, segment_type, tablespace_name,
bytes/1048576 AS alloc_mib
FROM dba_segments
WHERE owner='APP_USER' AND segment_name='ORDERS';
| SEGMENT_NAME | SEGMENT_TYPE | TABLESPACE_NAME | ALLOC_MIB |
|---|---|---|---|
| ORDERS | TABLE | APP_DATA | 2 |
Here the row is a nonpartitioned table segment. Inspect PARTITION_NAME and the segment types when the object is partitioned. Find associated indexes through DBA_INDEXES, and LOB segments through DBA_LOBS and the appropriate partition views, rather than assuming they share the table's segment name. One MiB is 1,048,576 bytes. One KiB is 1,024 bytes. See DBA_SEGMENTS.
Map extents to files
SELECT extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner='APP_USER' AND segment_name='ORDERS'
AND segment_type='TABLE'
ORDER BY extent_id;
| EXTENT_ID | FILE_ID | BLOCK_ID | BLOCKS |
|---|---|---|---|
| 0 | 7 | 128 | 128 |
| 1 | 8 | 128 | 128 |
FILE_ID is the absolute containing-file number, BLOCK_ID is the extent's starting block, and BLOCKS is its length in Oracle blocks. For partitioned objects, include the relevant PARTITION_NAME and segment type when selecting and ordering rows.
Both extents belong to APP_USER.ORDERS in APP_DATA. Uniform extents are 1 MiB. With an 8 KiB block, each extent has 128 blocks. Block numbers 128 through 255 are consecutive within each file. File ID and block number together identify the location. Block 128 in file 7 is distinct from block 128 in file 8. The two extents account for 2 MiB of allocated segment storage. The trace is a relationship to the containing file. It does not move or copy stored data. See DBA_EXTENTS.
SELECT e.extent_id, e.file_id, e.block_id, e.blocks,
f.tablespace_name, f.file_name
FROM dba_extents e
JOIN dba_data_files f ON f.file_id=e.file_id
WHERE e.owner='APP_USER' AND e.segment_name='ORDERS'
AND e.segment_type='TABLE'
ORDER BY e.extent_id;
This join resolves the extent's file identity to a filename in the same container. A segment's header file alone is insufficient to locate all of its extents.
Check existing space and possible growth
SELECT file_id, file_name, bytes/1048576 AS mib,
autoextensible AS auto, maxbytes/1048576 AS max_mib
FROM dba_data_files
WHERE tablespace_name='APP_DATA'
ORDER BY file_id;
| FILE_ID | FILE_NAME | MIB | AUTO | MAX_MIB |
|---|---|---|---|---|
| 7 | /data/app01.dbf | 64 | YES | 64 |
| 8 | /data/app02.dbf | 64 | YES | 64 |
BYTES is the current file size. AUTOEXTENSIBLE enables automatic growth, and MAXBYTES bounds it. These files have reached their maximum automatic size. Their total file bytes also include file metadata. USER_BYTES describes the portion available for user data. Neither file size nor maximum size directly measures free extents or free space inside a segment's existing blocks. See DBA_DATA_FILES and managing data files and temp files.
Confirm file status and growth increments when investigating capacity:
SELECT file_id, online_status, increment_by, user_bytes
FROM dba_data_files
WHERE tablespace_name='APP_DATA'
ORDER BY file_id;
INCREMENT_BY uses Oracle blocks. Growth must satisfy its increment, file limits, and storage constraints. Other checks may include PDB storage limits, platform and file limits, permitted file counts, and filesystem or ASM restrictions. AUTOEXTEND=YES alone is insufficient to conclude that future allocation will succeed.
SELECT NVL(SUM(bytes),0)/1048576 AS free_mib
FROM dba_free_space
WHERE tablespace_name='APP_DATA';
| FREE_MIB |
|---|
| 0 |
DBA_FREE_SPACE inventories free extents. A file with no free space has no row in this view. The scalar aggregate converts an empty result to zero. Here every other available extent is already allocated, all relevant files are online, and visibility is confirmed, so free space is zero. Offline locally managed files and incomplete visibility can also hide extent information, so establish those conditions before interpreting a missing row. See DBA_FREE_SPACE.
When free extents exist, inspect the largest available extent as well as the total free bytes:
SELECT file_id, SUM(bytes)/1048576 AS free_mib,
MAX(bytes)/1048576 AS largest_free_mib
FROM dba_free_space
WHERE tablespace_name='APP_DATA'
GROUP BY file_id
ORDER BY file_id;
The next extent must fit in one file. A sufficient total spread across small free ranges can still fail to supply the required contiguous block range. Space already allocated inside another segment is unavailable for this segment's next extent.
Check the storage backing the exact file
For these filesystem files:
df -h /data/app01.dbf
df -h /data/app02.dbf
Verify both actual file locations, their mounts, and the available capacity. Both files are on /data. The selected output columns show 12G available and 88% used. Linux df -h uses binary-scaled human-readable values. These are rounded display values. Use exact byte units when planning a specific allocation. Check inodes or filesystem limits when relevant. For ASM filenames, inspect ASM disk-group capacity instead of a filesystem report. Usable growth capacity must respect redundancy and reserve requirements.
The failed allocation needs 128 more blocks, a 1 MiB extent at an 8 KiB block size. ORA-01653 reports failure to allocate the required extent for the named table segment in the named tablespace. Here there are no free extents, and both files are at their automatic-growth maximum. Free filesystem capacity can support an appropriate capacity change, but it is not already allocated to the tablespace. Select a remedy only after reviewing growth limits, storage headroom, database requirements, and the operational change plan. See ORA-01653.
In the converse case, the current file size is 64 MiB, the automatic maximum is 128 MiB, and the filesystem has zero free space. The file has a configured growth allowance, but new physical bytes cannot be supplied. Existing free extents or usable space inside the segment could still satisfy other requests. This case concerns a request that requires file growth.
A quota error such as ORA-01536 requires inspecting the user's tablespace quota. That is a further allocation condition. A quota does not reserve physical space. Diagnose the actual error before selecting a capacity remedy.
Other files and their purpose
| File type | Purpose and relevant inspection |
|---|---|
| Data files | Persistent table, index, and other segment storage. Inspect DBA_DATA_FILES and the relevant extent and segment rows. |
| Tempfiles | Temporary work storage. Inspect DBA_TEMP_FILES and temporary-space views. A permanent data-file inventory excludes tempfiles. |
| Control files | Database structure and recovery metadata, including data-file and redo-file records. |
| Online redo and archived redo | Changes used for recovery. Archived redo availability depends on the database's archive configuration. |
| Password file | Administrative authentication, including supported remote administrative connections. Separate from ordinary application table storage. |
| Block change tracking file | Optional support for locating changed blocks during incremental backups. Existence and configuration are separate from the table's segment allocation. Check release, edition, and feature eligibility before enabling features. |
These files can consume storage in their own locations. Inspect the exact file type and backing storage rather than treating every database file as an extent of ORDERS. Never remove Oracle files from the operating system to resolve a capacity alert. See physical storage structures, DBA_TEMP_FILES, and database system files (page 18).
Recap
Trace the failing segment to its one tablespace and each extent to a file and block range. Compare free extents and the largest contiguous free range with current file size, automatic growth, and the maximum size, then compare that limit with the filesystem or ASM capacity behind the exact file. A tablespace can be full while the filesystem still has room, and a file that is allowed to grow can still be blocked when the backing storage has no free space.
No comments:
Post a Comment