A failed insert can involve extent allocation, a user's quota or an unusable index. A slow lookup can involve extra block visits. Capture the exact error, operation, object and time before choosing a change. The useful distinction is between reusable room inside a segment, free extents available for growth and an index's availability.
Oracle DBA Lesson 59 Part 1 — Identify the Fault Before Choosing a Space Fix
Reusable space and allocation
A segment owns extents containing database blocks. In automatic segment space management (ASSM), bitmaps help locate blocks with suitable room for rows. An extent already allocated to a table can contain reusable room without appearing in DBA_FREE_SPACE. These logical structures are backed by physical data files on disk. The schematic blocks are conceptual, not memory buffers or measured storage maps.
When growth needs another extent, Oracle searches for a suitable logically contiguous range within one data file. One extent cannot span files. “Contiguous” here describes Oracle file blocks; filesystem or RAID layout need not place the corresponding sectors physically together.
An ORA-01653 allocation request needs 128 blocks. The tablespace uses 8 KiB blocks, so the requested allocation is 1 MiB. Four free ranges of 64 blocks each provide 256 blocks, or 2 MiB total, but the largest range is only 0.5 MiB. The ranges are separated by allocated space, no other suitable range exists, and the files cannot extend. None satisfies this request. In a locally managed tablespace, adjacent free space is tracked automatically; do not infer that a manual coalesce or index rebuild is the required remedy.
See logical storage structures and ORA-01653.
Compare total free space and the largest extent
Check the current container, then compare total free space with the largest free extent:
SHOW CON_NAME
SELECT tablespace_name,
ROUND(SUM(bytes)/1048576, 2) AS free_mib,
ROUND(MAX(bytes)/1048576, 2) AS largest_mib
FROM dba_free_space
WHERE tablespace_name = 'APP_DATA'
GROUP BY tablespace_name;For APP_DATA the grouped result is:
| TABLESPACE_NAME | FREE_MIB | LARGEST_MIB |
|---|---|---|
| APP_DATA | 2.00 | 0.50 |
SUM and MAX answer different allocation questions. Keep the unrounded block/byte evidence for precise decisions. The calculation uses MiB; the narration uses the familiar word megabytes. Check the actual tablespace block size rather than assuming 8 KiB everywhere.
DBA_FREE_SPACE reports free extents. A file with no free extents contributes no rows; offline locally managed files also have visibility limitations. An empty grouped result requires checking tablespace identity, file state and dictionary visibility. It is not a complete health diagnosis.
For per-file detail:
SELECT file_id, block_id, blocks, bytes
FROM dba_free_space
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id, block_id;Compare this evidence with the requested allocation, data-file sizes, AUTOEXTENSIBLE and MAXBYTES limits, and actual filesystem/ASM capacity. If the error is ORA-01536, inspect the affected user's quota. More configured growth allowance still needs physical storage.
To inspect existing allocation:
SELECT segment_name, segment_type, partition_name,
tablespace_name, bytes, blocks, extents
FROM dba_segments
WHERE owner = 'APP_USER'
AND segment_name = 'EVENTS';DBA_SEGMENTS reports allocated segment storage. Its BYTES value does not measure the sum of live row lengths or the exact reusable room inside blocks. More detailed internal-space analysis requires an appropriate method such as DBMS_SPACE and the relevant privileges.
See DBA_FREE_SPACE, DBA_SEGMENTS, and managing space for schema objects.
Inspect index state at the correct level
An error naming an unusable index requires inspection of that index. For ordinary B-tree indexes:
SELECT index_name, index_type, partitioned, status
FROM dba_indexes
WHERE owner = 'APP_USER'
ORDER BY index_name;The index rows are:
| INDEX_NAME | INDEX_TYPE | PARTITIONED | STATUS |
|---|---|---|---|
| EVENTS_IX | NORMAL | NO | UNUSABLE |
| EVENTS_P_IX | NORMAL | YES | N/A |
VALID and UNUSABLE describe a nonpartitioned index's availability. A partitioned parent's N/A directs you to lower-level status; it is not by itself a broken-index diagnosis.
SELECT index_name, partition_name, status
FROM dba_ind_partitions
WHERE index_owner = 'APP_USER'
AND index_name = 'EVENTS_P_IX';The partition rows are:
| INDEX_NAME | PARTITION_NAME | STATUS |
|---|---|---|
| EVENTS_P_IX | P1 | USABLE |
| EVENTS_P_IX | P2 | UNUSABLE |
For subpartitioned indexes inspect DBA_IND_SUBPARTITIONS as well:
SELECT index_name, partition_name, subpartition_name, status
FROM dba_ind_subpartitions
WHERE index_owner = 'APP_USER'
AND index_name = 'EVENTS_P_IX';Confirm index type and relevant constraints. Domain/function-based or composite-partitioned structures can need additional checks.
See ALL_INDEXES for index type, partitioning, and status. DBA_INDEXES supplies database-wide scope. See managing indexes for unusable indexes and the N/A parent status, and ALL_IND_PARTITIONS for partition status. DBA_IND_PARTITIONS shares those column definitions.
Interpret application impact
An unusable index normally needs rebuilding or re-creation before it can serve as an index access path. Adding storage alone does not restore that state. The effect on a particular operation depends on constraints, index role, parameter settings and forced access. SKIP_UNUSABLE_INDEXES defaults to TRUE in 19c, with documented exceptions for unusable unique indexes used for constraint enforcement; forced index hints can also produce ORA-01502. Inspect the actual setting and operation rather than assuming every SELECT must fail.
NOLOGGING governs redo behavior for supported operations, subject to factors such as force logging. It does not itself request UNUSABLE state. Investigate relevant maintenance, loads or explicit state changes; treat the status as evidence, not a guess from the logging attribute.
See SKIP_UNUSABLE_INDEXES and logging_clause.
Slow lookups and row organization
When an updated row no longer fits its original block but fits another, Oracle can migrate it and leave a forwarding address. The original rowid is retained. Following the pointer can require an additional block visit; a logical visit need not cause a physical disk read on every access. Chaining can also split a row across blocks, including cases where a row cannot fit one block.
Measure the affected SQL and row evidence over a meaningful interval. A cumulative chained-row count or free-space total alone cannot attribute a current slowdown. An index rebuild reconstructs index storage; it does not reorder the table's rows. Clustering factor describes row distribution across table blocks as rows are followed in index order, so rebuilding an index does not automatically correct poor table clustering.
See logical storage structures for migrated and chained rows, and ALL_INDEXES for clustering factor.
Recap
Distinguish reusable room inside a segment, free extents that can satisfy growth, and an index that is unavailable. A new extent needs one suitable contiguous range in a single data file. With 8 KiB blocks, a 128-block ORA-01653 request is 1 MiB. Four separated 64-block ranges total 2 MiB, and the largest is 0.5 MiB, so none of them satisfies the request when the files cannot extend. SUM and MAX on DBA_FREE_SPACE answer those two questions. DBA_SEGMENTS.BYTES is allocated storage, not live row length. ORA-01536 points to the user's quota, and configured growth still needs physical storage.
Read a nonpartitioned index as VALID or UNUSABLE. A partitioned parent's N/A means you inspect partitions and, where applicable, subpartitions. An unusable index needs rebuilding or re-creation; NOLOGGING does not request that state, and adding space does not clear it. SKIP_UNUSABLE_INDEXES defaults to TRUE in 19c, with documented exceptions. Migration or chaining can add a block visit, and that hypothesis needs measurement. Rebuilding an index does not reorder the table or repair clustering factor.
No comments:
Post a Comment