Lesson 6	Tablespaces
Objective	Explain how Oracle manages space using tablespaces, datafiles, and segments.

Oracle Tablespaces: The Logical Layer of Storage

A tablespace is Oracle’s logical storage container. It sits between database objects (tables, indexes, LOBs, etc.) and the physical files on disk. By targeting a tablespace when you create or alter an object, you let Oracle place and grow that object across one or more datafiles that belong to the same tablespace.

Why tablespaces matter

Organization: Group related objects (OLTP vs. reporting, app modules, hot vs. cold data) for cleaner administration.
Performance & I/O: Spread data across datafiles (and therefore disks/LUNs) to improve throughput and reduce contention.
Growth control: Add datafiles or enable AUTOEXTEND without touching individual objects.
Backup/recovery scope: Back up or restore a subset of the database by tablespace.
Security/quotas: Grant user quotas per tablespace to control consumption.

How Oracle lays out data

Oracle persists objects as segments (e.g., a table segment, index segment). Segments grow in extents, and extents are collections of blocks. The key relationships are:

Tablespace ↔ Datafiles: one tablespace has many datafiles; each datafile belongs to one tablespace.
Segment ↔ Datafiles (within a tablespace): a segment can span multiple datafiles; each datafile holds many segments. This creates a many-to-many relationship mediated by the tablespace.

Visual mappings

Tables A/B in Tablespace A; Table C in Tablespace B; data may span multiple datafiles within a tablespace. — Tables and indexes live in tablespaces. Each tablespace owns one or more datafiles. A single segment may span multiple datafiles in its tablespace; each datafile can store many segments.

Figure 3-6: Typical Oracle tablespaces (SYSTEM, SYSAUX, TEMP, UNDO, user tablespaces) — Figure 3-6: Typical tablespace layout-**SYSTEM** & **SYSAUX** (data dictionary & components), **TEMP** (sort/hash/joins), **UNDO** (read consistency & rollback), and **user/application** tablespaces.

Oracle Cloud DBA

Core tablespace types

SYSTEM/SYSAUX: mandatory; contain the data dictionary and component metadata (always online when the DB is open).
User/Application tablespaces: hold business tables, indexes, and LOBs.
TEMP: temporary segments for sort/merge/hash operations; sized for peak workload.
UNDO: maintains read consistency and supports transaction rollback/flashback operations.
Smallfile vs. Bigfile: Smallfile tablespaces contain many smaller datafiles; Bigfile tablespaces contain a single very large datafile-useful for very large databases and simplified storage management.

Creating and growing tablespaces

Smallfile example:


CREATE TABLESPACE app_tbs
  DATAFILE '/u01/oradata/APP_TBS01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 256M MAXSIZE 16G
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Bigfile example:


CREATE BIGFILE TABLESPACE app_big_tbs
  DATAFILE '/u01/oradata/APP_BIG_TBS01.dbf' SIZE 64G
  AUTOEXTEND ON NEXT 4G MAXSIZE 1T
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Add space later:


ALTER TABLESPACE app_tbs
  ADD DATAFILE '/u01/oradata/APP_TBS02.dbf' SIZE 2G AUTOEXTEND ON;

Placing objects into tablespaces

Specify the target tablespace when creating objects. Indexes are independent segments and can live in a different tablespace than their table.


CREATE TABLE sales (
  id        NUMBER PRIMARY KEY,
  sold_at   DATE,
  amount    NUMBER(12,2)
) TABLESPACE app_tbs;

CREATE INDEX sales_sold_at_ix ON sales(sold_at) TABLESPACE app_idx_tbs;

Inspecting mappings with views

DBA_TABLESPACES – properties and status of tablespaces.
DBA_DATA_FILES, DBA_TEMP_FILES – datafile/tempfile names, sizes, autoextend.
DBA_SEGMENTS – which tablespace a segment lives in and how large it is.
DBA_EXTENTS – extent-level placement (which file, which blocks).

Examples:


SELECT tablespace_name, contents, bigfile
FROM   dba_tablespaces
ORDER  BY tablespace_name;

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, autoextensible
FROM   dba_data_files
ORDER  BY tablespace_name, file_name;

SELECT owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 AS size_mb
FROM   dba_segments
WHERE  tablespace_name = 'APP_TBS'
ORDER  BY size_mb DESC;

Best-practice guidance

Separate roles: Consider distinct tablespaces for data, indexes, and large LOBs when it aids maintenance or I/O.
Use locally managed + ASSM: Prefer EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for efficiency.
Right-size TEMP & UNDO: Size for peak operations; monitor with AWR/ASH and V$ views.
Plan growth: Use AUTOEXTEND judiciously and watch MAXSIZE; track free space with DBA_FREE_SPACE.
Bigfile selectively: Great with ASM and thin provisioning; ensure backup tooling understands very large files.

Clarifying a common misconception

It’s accurate to say “many objects can be stored in a tablespace, and a tablespace uses many datafiles.” More precisely: a segment (table, index) resides in exactly one tablespace but may span multiple datafiles of that tablespace as it grows; each datafile stores extents from many segments.

Quick mapping answers

How is a tablespace mapped to datafiles? One-to-many (one tablespace → many datafiles; each datafile → one tablespace).
How do objects map to datafiles? Many-to-many within the tablespace (a segment spans multiple datafiles; a datafile contains many segments).