Database Storage Structures and Space Management
This domain tests the logical-to-physical storage hierarchy and the diagnostic queries that explain why a segment cannot grow. Most questions reduce to a symptom such as ORA-01653 or ORA-01652 and the view that proves the cause.
The hierarchy
Database, then tablespace, then segment, then extent, then Oracle block. An extent is a set of contiguous blocks; a segment is a set of extents in one tablespace. Locally managed tablespaces track free space in a bitmap in the datafile header, and dictionary-managed tablespaces (using FET$ and UET$) are deprecated. With AUTOALLOCATE the server chooses extent sizes; with UNIFORM SIZE every extent is identical. Automatic Segment Space Management tracks free space per block with bitmaps; without it the segment uses freelists and PCTUSED applies. Bigfile tablespaces have exactly one datafile, must be locally managed, and are resized with ALTER TABLESPACE rather than by adding files. Temporary tablespaces use tempfiles, and undo requires an undo tablespace.
Views you must know
DBA_TABLESPACES, DBA_DATA_FILES, DBA_TEMP_FILES, DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE, DBA_TS_QUOTAS, DBA_TABLESPACE_USAGE_METRICS.
Worked example
An insert fails with ORA-01653: unable to extend table APP.ORDERS by 1024 in tablespace USERS, yet DBA_FREE_SPACE reports plenty of space. Reasoning order: first check DBA_TS_QUOTAS for the user's quota on USERS, since a quota of zero blocks allocation regardless of free space; second check DBA_DATA_FILES for AUTOEXTENSIBLE and whether MAXBYTES is reached; third compare the largest contiguous free extent with the next extent size. Here the quota is zero, so the fix is ALTER USER app QUOTA UNLIMITED ON users.
Traps that cost marks
- Deleting rows does not lower the high-water mark; only TRUNCATE, DEALLOCATE UNUSED, or a shrink does.
- PCTFREE reserves room for updates and has no effect on PCTUSED in an ASSM tablespace.
- A user with no quota cannot create a segment even in the default tablespace.