Oracle DBA Revision 6 — Lessons 43 to 47 and 56 to 65

Contents

Storage allocationGrowth and quotasFile capacityTemporary spaceUndo and consistent readsSpace reuse

Storage allocation

A tablespace groups logical storage and is backed by physical files. A segment holds the allocated space for an object such as a table, an index or a partition. It grows by receiving extents. Each extent contains a set of logically contiguous database blocks and stays within one file.

Tablespace
Logical container→Segment → extents
Space allocated to an object→Database blocks
Each extent stays in one file

A segment belongs to one tablespace. Its extents can occupy several files in that tablespace. A smallfile tablespace can have several files. A bigfile tablespace has one datafile or tempfile, with different supported size limits. File capacity depends on the tablespace type, block size and platform.

Example

An INVENTORY table has extents in two files of the same tablespace. The table remains one segment. An index on that table has its own segment and can use a different tablespace.

In SQL*Plus, connect to the intended PDB as the object owner to inspect its allocated space. Replace the object name with an existing table:

SELECT segment_name, segment_type, tablespace_name,
       bytes / POWER(1024, 2) AS allocated_mib,
       extents
FROM user_segments
WHERE segment_name = 'INVENTORY';

ALLOCATED_MIB measures segment allocation, including space that may be reusable. Live rows occupy part of that allocation. A newly created table using deferred segment creation may have no segment until an insert requires one.

For file-level extent placement, an account with SELECT access to DBA_EXTENTS can query the same PDB:

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

FILE_ID identifies the datafile; BLOCK_ID is the extent’s starting block. USER_EXTENTS does not expose these file-position columns in the Oracle 19c reference. Use DBA_EXTENTS when that placement is required. Offline locally managed files can affect which extents this view reports.

Growth and quotas

A user’s default tablespace supplies the location for a new table. The CREATE TABLE privilege allows table creation, and a quota limits the user’s allocation in that tablespace. Changing the default leaves existing tables in their current locations.

User

Required privilege and enough quota for the allocation.

Tablespace

Space for an extent of the required size, or a usable way to grow.

File and storage

Automatic extension, configured maximum and physical capacity.

As the object owner, inspect explicit quotas:

SELECT tablespace_name,
       bytes / POWER(1024, 2) AS charged_mib,
       max_bytes
FROM user_ts_quotas;

SELECT privilege
FROM session_privs
WHERE privilege = 'UNLIMITED TABLESPACE';

MAX_BYTES is the quota in bytes; −1 means unlimited for that quota. UNLIMITED TABLESPACE can override explicit quotas, so an absent quota row alone is not a complete privilege diagnosis.

With SELECT access to DBA_DATA_FILES in the intended PDB:

SELECT file_id, tablespace_name,
       bytes / POWER(1024, 2) AS current_mib,
       autoextensible,
       maxbytes / POWER(1024, 2) AS maximum_mib
FROM dba_data_files
ORDER BY tablespace_name, file_id;

For an autoextensible file, compare current allocation with its configured maximum. The possible increase requires available filesystem or ASM capacity; the maximum setting reserves no physical space. A file with automatic extension disabled can still have unallocated space inside its current size. Tempfiles are listed in DBA_TEMP_FILES.

Example

A user has used its entire 2 GiB quota while the tablespace has 6 GiB free. The user can still fail to allocate another extent. Raising the quota increases the user’s allowance. Further growth also requires space in the tablespace or usable file extension.

File capacity

In DBA_DATA_FILES, BYTES is current file size, MAXBYTES is the configured automatic-growth ceiling, and INCREMENT_BY counts database blocks. Multiply INCREMENT_BY by the tablespace block size to obtain the extension increment in bytes. Smallfile tablespaces can grow by extending or adding files; a bigfile tablespace grows through its single file.

Oracle Managed Files automates supported file naming, creation and removal through database operations. Physical storage capacity and file-growth limits still govern allocation.

Temporary space

A sort or hash operation uses a work area in the server process’s PGA, the program global area. When the operation cannot fit in its available work area, intermediate results can spill into temporary segments in tempfiles. Completed work can release its temporary allocation for reuse while the tempfile remains at its existing size.

PGA

Private work areaSort or hash work in process memory.

TEMP

Temporary segmentsIntermediate results in physical tempfiles.

To inspect current temporary consumers on a single instance, use an account with SELECT access to V_$TEMPSEG_USAGE and DBA_TABLESPACES in the intended PDB:

SELECT u.username, u.sql_id_tempseg, u.segtype,
       u.tablespace,
       ROUND(u.blocks * t.block_size / POWER(1024, 2), 2)
         AS allocated_mib
FROM v$tempseg_usage u
JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace
WHERE u.con_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'CON_ID'))
ORDER BY allocated_mib DESC;

The block count is multiplied by that tablespace’s block size. SQL_ID_TEMPSEG identifies the SQL statement that created the temporary segment. Empty results can mean that no matching temporary allocation exists at that instant. For RAC, inspect all relevant instances and preserve instance/container identity in the joins.

Temporary tables

Global temporary tables have a persistent shared definition and session-private rows. Private temporary tables keep both definition and rows private to the session.

Table	Clause	Lifetime
Global temporary	ON COMMIT DELETE ROWS	Rows end at transaction commit; definition remains
Global temporary	ON COMMIT PRESERVE ROWS	Rows remain for the session, unless explicitly removed earlier
Private temporary	ON COMMIT DROP DEFINITION	Definition and rows end with the transaction
Private temporary	ON COMMIT PRESERVE DEFINITION	Definition and rows remain for the session

Note. Temporary undo stores undo for temporary objects in TEMP and reduces the associated permanent undo and redo. Changes to permanent objects continue to use permanent undo. TEMP_UNDO_ENABLED requires COMPATIBLE of at least 12.0.0. Set the intended mode in a fresh session before temporary-object use; changing it after the session is already using one undo mode may have no effect.

Watch Oracle DBA Revision 6 — Lessons 43 to 47 and 56 to 65 on YouTube

Undo and consistent reads

Undo stores information needed to reverse changes. Rollback uses it to undo transaction work. Consistent reads use it to reconstruct versions that match a query’s snapshot. A commit makes a transaction’s changes visible to later statements, while retained undo can still serve earlier readers.

Example

Query starts
Committed price: 80→Another commit
New price: 95→Original query
Earlier version: 80

Under READ COMMITTED, the snapshot belongs to each statement. A second query started after the commit can see 95. The first query can need undo to reconstruct 80 if it encounters a newer block version.

An account with SELECT access to V_$UNDOSTAT can inspect recent interval statistics:

SELECT begin_time, end_time, maxquerylen,
       tuned_undoretention, ssolderrcnt, nospaceerrcnt
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 6 ROWS ONLY;

MAXQUERYLEN and TUNED_UNDORETENTION are seconds. SSOLDERRCNT counts snapshot-too-old errors; NOSPACEERRCNT counts requests that could not obtain undo space because it was occupied by active transactions. Inspect the relevant instance/container and time interval. Combine these counters with workload, query duration and undo-generation measurements to size the undo tablespace.

UNDO_RETENTION is a retention threshold subject to available capacity. From Oracle 19c RU 19.7, its behavior includes fixed-size and autoextensible undo tablespaces. Earlier 19c fixed-size behavior differs. With retention guarantee disabled, capacity pressure can cause unexpired undo to be reused. RETENTION GUARANTEE protects unexpired undo, but new writes can then fail if no space is available.

Note. In local undo mode, each PDB uses its own undo. In shared undo mode, undo belongs to the root, and its parameter scope follows that ownership. From 19c RU 19.9, a changed root UNDO_RETENTION value is not inherited by PDBs; check the intended PDB explicitly. PDB-level changes require local undo mode.

Space reuse

Free room inside a segment can serve future rows. Extents released to a tablespace can serve other segments. A file resize can return capacity to the underlying storage when its ending region is free.

State	What it permits	What to inspect
Free room inside segment blocks	Future rows can reuse suitable space	Segment/block usage and high-water mark
Extents returned to tablespace	Other segments can receive that space	Tablespace free extents
Free space at the end of a file	A permitted resize may reduce the physical file	Highest allocated block and file constraints

The high-water mark tracks how far a segment has used blocks. A table can have few rows while a full scan still visits blocks below that mark. Eligible segment shrink can compact data and return space; SHRINK SPACE COMPACT postpones the final high-water-mark adjustment and deallocation. Shrink has object and ASSM eligibility requirements.

A table move can change row locations. Index consequences depend on the operation and its clauses; verify index status after the operation instead of assuming every index remains usable. Choose an operation by its effect: shrink compacts eligible segments, a move relocates a segment, compression changes the storage representation, and resize changes file size. Check release and object restrictions for the selected operation. Deferred segment creation and Basic Table Compression are unavailable in Standard Edition 2. Advanced Row Compression requires the Advanced Compression option in applicable Enterprise Edition deployments.

Resumable statements

An eligible statement in a resumable session can suspend after certain space-allocation errors. The administrator can correct the quota or capacity condition before the timeout, allowing execution to resume. Enable it for the intended session with the RESUMABLE privilege and choose a timeout appropriate to the operation. A suspended operation can retain resources and locks.

References

• Logical storage structures
• Tablespaces and quotas
• Datafiles and tempfiles
• Segment space and shrink
• Tables and temporary-table lifetimes
• PGA work areas
• Statement read consistency
• Undo management
• Undo retention and release updates
• Temporary undo
• Extent placement
• Tablespace quotas
• File allocation and growth fields
• Temporary-space consumers
• Undo statistics
• Owned segment allocation
• Oracle Managed Files
• Feature availability and licensing

https://www.apps-dba.com/https://www.youtube.com/@EnhanceSkillsDaily