Create and Manage Database Storage Structures
The logical ladder
Database → tablespaces → segments → extents → blocks; each tablespace maps to one or more datafiles. Every database needs SYSTEM (data dictionary), SYSAUX (AWR, OEM repository, components), an undo tablespace and a TEMP tablespace.
Space management
Locally managed tablespaces track extent allocation in a bitmap in the datafile header and have replaced dictionary-managed tablespaces (UET$/FET$), which are obsolete. ASSM (automatic segment space management) records block free space in bitmaps; MSSM uses freelists and honours PCTUSED. Under ASSM, PCTFREE still applies but PCTUSED is ignored - a favourite exam trap.
Bigfile tablespaces contain exactly one datafile (up to 4G blocks) and must be locally managed with ASSM; smallfile is the default. DB_BLOCK_SIZE is fixed at creation; any other block size needs the matching db_nk_cache_size in the SGA.
Symptoms and fixes
- ORA-01653/01654: a table or index could not extend - add space, enable AUTOEXTEND, or raise the user's quota.
- ORA-01652: cannot extend a TEMP segment - enlarge the temporary tablespace or hunt a runaway sort.
- Row migration: an update made a row too large for its block, so it moved and left a pointer - prevent it with adequate PCTFREE, repair with
ALTER TABLE ... MOVE. - Row chaining: the row is simply larger than the block - unavoidable.
Rule of thumb: when a segment cannot grow, check three separate things - free space in the tablespace, AUTOEXTEND on the datafile, and the user's quota. Each has its own error and its own fix.