Storage Structures and Space Management
Storage Structures and Space Management asks how Oracle maps logical space onto files and how you diagnose extension failures.
Logical to physical
A tablespace contains segments (table, index, undo, temporary, LOB), segments contain extents, extents contain blocks of db_block_size (8KB typical, maximum 32KB). Datafiles, tempfiles and OS blocks form the physical layer. Dictionary-managed tablespaces are legacy; locally managed tablespaces with ASSM bitmap free-space management are the norm, and under ASSM PCTUSED is irrelevant.
Space and extent rules
SMALLFILE tablespaces hold many datafiles, BIGFILE one; AUTOALLOCATE lets Oracle size extents, UNIFORM fixes them. Deferred segment creation, on by default, means a new table gets a segment only at first insert, so dba_segments can show nothing for a table that exists. Space can be reclaimed with ALTER TABLE SHRINK SPACE (ASSM, and row movement for some cases) or ALTER TABLE MOVE. Key views: dba_data_files, dba_free_space, dba_segments, dba_tablespace_usage_metrics, dba_temp_free_space.
Worked example
A load fails with ORA-01653: unable to extend table APP.LOG by 1024 in tablespace APP_DATA. Query dba_data_files for that tablespace and note maxbytes equals bytes, meaning autoextend is capped. Fix by ALTER DATABASE DATAFILE ... AUTOEXTEND ON NEXT 100M MAXSIZE 20G, or add a datafile with an explicit SIZE, or enable resumable space allocation before rerunning. Rerun only after the space question is settled.
Traps
Confusing a tablespace with its datafiles; forgetting that temporary tablespaces use tempfiles; assuming DROP TABLE makes the datafile smaller; ignoring the 32KB block-size ceiling; expecting PCTFREE behaviour identical to non-ASSM segments; overlooking that a table can exist without a segment.