Managing Database Storage Structures
Scope
This domain covers tablespaces, datafiles, segments, extents, blocks, Oracle Managed Files, bigfile tablespaces, and space management. Expect questions on which storage object can be taken offline and how space is allocated.
Core rules
- A tablespace contains segments; a segment contains extents; an extent contains contiguous Oracle blocks. A datafile belongs to exactly one tablespace.
- Locally managed tablespaces use a bitmap in the tablespace; dictionary-managed tablespaces are legacy. Automatic Segment Space Management (ASSM) uses bitmaps for block-level free space.
- Smallfile tablespaces can contain many datafiles; bigfile tablespaces contain one datafile and use 32K block addressing. AUTOEXTEND grows a file on demand but MAXSIZE caps it.
- Undo tablespaces hold undo segments; temporary tablespaces use tempfiles. SYSTEM cannot be taken offline. SYSAUX is mandatory for many components.
- Views: DBA_TABLESPACES, DBA_DATA_FILES, DBA_TEMP_FILES, DBA_SEGMENTS, DBA_EXTENTS, V$TABLESPACE.
Worked example
CREATE TABLESPACE app DATAFILE '/u01/oradata/ORCL/app01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G SEGMENT SPACE MANAGEMENT AUTO; creates an ASSM tablespace. ALTER TABLESPACE app ADD DATAFILE ... adds space. ALTER TABLE hr.employees MOVE TABLESPACE app relocates the segment and its indexes must be rebuilt unless unusable.
Traps
SYSTEM cannot be offline. A tempfile is not a datafile. A bigfile tablespace cannot have multiple datafiles. AUTOEXTEND is not unlimited unless MAXSIZE UNLIMITED is set. Moving a table does not automatically move its indexes.