Tablespaces, Datafiles & Extents: Oracle Storage Architecture

TAKEAWAYS:

• Tablespaces are logical storage units that group related objects (tables, indexes) and define storage characteristics (extent size, growth policies).
• Datafiles are physical files on disk that store tablespace data, with each tablespace requiring at least one datafile.
• Extents are contiguous blocks of storage allocated to objects, preventing fragmentation via pre-allocation (chaining/migration).
• UNDO tablespaces are critical for transaction rollback; retention policies balance recovery time and storage overhead.
• Commands like CREATE TABLESPACE, ALTER DATABASE, and DROP TABLESPACE manage storage dynamically.
• Poor extent sizing or excessive chaining degrades performance, while proper tuning ensures efficient I/O and scalability.

1. Tablespaces: Logical Storage Containers

A tablespace is a logical storage unit in Oracle that groups database objects (tables, indexes, clusters) and defines their storage characteristics. It abstracts physical storage details, allowing administrators to manage space efficiently.

Key Concepts

• Purpose: Organize data by type (e.g., USERS for application tables, UNDO for transaction logs).
• Types:
  • Permanent tablespaces: Store user data (e.g., SYSTEM, USERS).
  • Temporary tablespaces: Hold temporary sort/join data (e.g., TEMP).
  • Undo tablespaces: Manage transaction rollback segments (critical for ACID compliance).
  • Rollback segments (legacy): Pre-Oracle 11g; replaced by undo tablespaces.

How Tablespaces Work

1. Logical Grouping: Objects in a tablespace share the same storage attributes (e.g., PCTFREE, PCTUSED).
2. Physical Storage: Each tablespace maps to one or more datafiles (physical files on disk).
3. Extent Allocation: Oracle allocates extents (contiguous blocks) to objects within the tablespace.

Example: Banking Transaction Logs

• Scenario: A bank uses Oracle to log transactions (deposits, withdrawals).
• Tablespace Design:
  • TRANSACTIONS tablespace stores transaction tables.
  • UNDO_TRX tablespace ensures transactions can be rolled back if failed.
  • TEMP_TRX handles temporary sorting during large reports.

Creating a Tablespace

CREATE TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/XE/users01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  DEFAULT STORAGE (INITIAL 1M NEXT 10M MINEXTENTS 1 MAXEXTENTS 2147483645
                   PCTINCREASE 0);

• Key Parameters:
  • DATAFILE: Specifies the physical file location and initial size.
  • AUTOEXTEND: Automatically grows the datafile if space runs out.
  • DEFAULT STORAGE: Sets default extent size for objects in this tablespace.

2. Datafiles: Physical Storage Files

A datafile is a physical file on disk that stores the actual data for a tablespace. Each tablespace must have at least one datafile.

Datafile Types

Datafile Management

• Adding a Datafile:

  ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/XE/users02.dbf' SIZE 1G;
  

• Dropping a Datafile (requires tablespace offline):

  ALTER TABLESPACE users OFFLINE NORMAL;
  ALTER TABLESPACE users DROP DATAFILE '/u01/app/oracle/oradata/XE/users01.dbf';
  ALTER TABLESPACE users ONLINE;
  

Real-World Analogy: Cloud Storage Buckets

• Example: Daraz’s inventory system uses Oracle tablespaces to separate:
  • PRODUCTS tablespace (datafiles on AWS S3).
  • UNDO_PRODUCTS tablespace (ensures order cancellations are reversible).
• Datafiles = S3 object storage; extents = contiguous blocks in S3 partitions.

3. Extents: Allocating Storage Blocks

An extent is a contiguous set of blocks allocated to a database object (table, index). Oracle pre-allocates extents to prevent fragmentation.

sequenceDiagram
    participant Oracle as Oracle Database
    participant Table as Transaction_Table
    participant Extent1 as Extent 1 (Block 1-10)
    participant Extent2 as Extent 2 (Block 11-20)

    Oracle->>Table: Allocate initial extent (1MB)
    Table->>Extent1: Store rows (Blocks 1-5)
    Table->>Extent1: Row exceeds block size (chaining)
    Table->>Extent2: Store remaining row data
    Oracle-->>Table: Extent allocation complete
    note right of Table: Result: Row chaining across Extent1 and Extent2
    note left of Oracle: Fix: Increase PCTFREE or use larger extents

Extent Allocation

1. Initial Allocation: When a table is created, Oracle allocates the first extent.
2. Dynamic Growth: If the object grows, Oracle allocates new extents (controlled by NEXT and PCTINCREASE).
3. Extent Size: Default is 1MB, but can be customized per tablespace.

Row Chaining and Migration

• Row Chaining: Occurs when a row exceeds the allocated block size, forcing Oracle to split it across extents (performance hit).
• Row Migration: Happens when a row is updated and doesn’t fit in its original block, forcing a move to a new block (also inefficient).

Preventing Chaining/Migration

• Increase PCTFREE: Allocates more free space per block (e.g., PCTFREE 20).
• Use Larger Extents: Reduces the number of extents per object.
• Partition Tables: Splits large tables into smaller, manageable chunks.

Worked Example: Kathmandu Traffic Routes

• Scenario: A traffic management app tracks vehicle routes using Oracle.
• Tablespace Design:
  • ROUTES tablespace (extents = city blocks).
  • If a route table grows beyond its initial extent (e.g., due to new GPS data), Oracle allocates a new extent.
  • Problem: If PCTFREE is too low, routes may chain/migrate, slowing queries.

-- Check for chaining in the ROUTES table
SELECT object_name, blocks, chained_rows
FROM dba_segments
WHERE object_name = 'ROUTES_TABLE';

4. UNDO Tablespaces: Transaction Rollback

UNDO tablespaces store undo data (before-images of rows) to enable:

• Transaction rollback (e.g., failed bank transfer).
• Query consistency (e.g., reading data as it was at a point in time).

UNDO Retention Policy

• Retention Time: How long undo data is kept (default: 900 seconds).

  ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/undo01.dbf'
  AUTOEXTEND ON NEXT 10M;
  ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/undo01.dbf'
  SET UNDO RETENTION 3600; -- 1 hour
  

• Impact:
  • High Retention: More storage used but better recovery.
  • Low Retention: Less storage but risk of "undo exception" (transaction fails to roll back).

Real-World Example: eSewa Transaction Reversal

• Scenario: A user initiates a payment via eSewa but disconnects mid-transaction.
• UNDO Role: If the transaction wasn’t committed, eSewa’s Oracle database uses undo data to reverse the debit from the user’s account.

5. Managing Tablespaces

Key Commands

Example: Adding a Datafile to USERS Tablespace

-- Step 1: Check current datafiles
SELECT tablespace_name, file_name, bytes/1024/1024 "Size (MB)"
FROM dba_data_files
WHERE tablespace_name = 'USERS';

-- Step 2: Add a new datafile
ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/XE/users03.dbf' SIZE 500M;

6. Performance Implications

In the Real World

1. Ncell’s Customer Data Storage

   • Idea: Ncell uses Oracle tablespaces to separate:
     • CUSTOMERS (datafiles on SSD storage).
     • UNDO_CUSTOMERS (ensures call drops don’t corrupt account data).
   • Extent Use: Each customer record is stored in extents sized for 1000 records, preventing chaining.

2. NEPSE’s Trade Transaction Logs

   • Idea: NEPSE’s Oracle database uses:
     • TRADES tablespace (datafiles on high-speed RAID arrays).
     • UNDO tablespace with 1-hour retention to allow rollback of failed trades.
   • Worked Example: If a stock trade fails mid-execution, NEPSE queries the UNDO tablespace to revert the trade within seconds.

3. Pathao’s Ride Dispatch System

   • Idea: Pathao’s backend uses Oracle tablespaces to:
     • DISPATCHES (stores ride assignments; extents sized for 5000 concurrent rides).
     • TEMP_DISPATCHES (handles temporary route calculations during peak hours).
   • Performance Tip: Pathao monitors chained_rows in the DISPATCHES table and increases PCTFREE to 30% during rush hours.

Exam Tip

• Focus Areas:

  1. Definitions: Clearly explain tablespaces, datafiles, and extents with examples (e.g., "A tablespace is like a folder in your computer, while a datafile is the actual file inside it").
  2. Commands: Memorize CREATE TABLESPACE, ALTER DATABASE, and DROP TABLESPACE syntax. Practice adding/removing datafiles.
  3. UNDO Retention: Know how to set retention time and its impact on recovery.
  4. Chaining/Migration: Describe scenarios where they occur and how to mitigate them (e.g., "Increase PCTFREE to 20%").
  5. Real-World Mapping: Relate tablespaces to cloud storage (e.g., "Daraz’s inventory tablespace = S3 bucket"), and extents to city blocks (e.g., "Kathmandu traffic routes").

• Common Pitfalls:

  • Confusing tablespaces (logical) with datafiles (physical).
  • Forgetting that UNDO tablespaces require separate management.
  • Not specifying AUTOEXTEND in CREATE TABLESPACE, leading to manual intervention.

• Practice Question:

  -- Write a script to:
  1. Create a tablespace `EMPLOYEES` with 2 datafiles (1G each, autoextend).
  2. Set UNDO retention to 2 hours.
  3. Check for chained rows in the `EMPLOYEES_TABLE`.
  

In the real world

• eSewa: Uses UNDO tablespaces to ensure transaction reversibility (e.g., if a payment fails, the system rolls back the deduction from the user’s account).
• Daraz’s Inventory System: Employs tablespaces to separate product data (PRODUCTS tablespace) from temporary sorting data (TEMP tablespace) during bulk operations, optimizing storage and performance.
• Nepal Rastra Bank (NRB) Core Banking: Relies on extent management to prevent fragmentation in critical tables (e.g., ACCOUNT_TRANSACTIONS), ensuring fast query responses during high-volume transactions like loan processing.