TABLESPACES

PERMANENT | UNDO | TEMPORARY


Tablespace — Overview

  • A tablespace is a logical storage unit made up of one or more datafiles.
  • It is the logical layer that maps to physical datafiles at the OS level — used for naming methodology and performance improvement.
  • Storage hierarchy: Tablespace → Datafile → Segment → Extent → Block.
Tablespace Types — By Type of Data
  • Permanent Tablespace — stores permanent objects (tables, indexes, views).
  • Undo Tablespace — maintains old/before-image data for rollback & read consistency.
  • Temporary Tablespace — used for sorting (PGA) operations like ORDER BY, GROUP BY, DISTINCT, UNION ALL.
Tablespace Types — By Size of Datafile
  • Small File Tablespace — one tablespace can have up to 1022 datafiles; each datafile can grow up to 128 GB (max datafile size depends on DB block size — e.g. 8K block size → max ~32 GB).
  • Big File Tablespace — one tablespace has exactly one datafile, which can grow up to 128 TB.

1) Permanent Tablespace

  • Used to store permanent database objects — tables, indexes, views, materialized views.
  • Data persists until explicitly dropped or deleted.
  • Two categories: System tablespace and Non-System (user) tablespace.
SYSTEM Tablespace
  • Always used to store system (dictionary) data, along with metadata.
  • Every Oracle database has its own SYSTEM tablespace — it is the first tablespace created when the database is created.
  • Accessing it requires a higher level of privilege (SYS).
  • Cannot be renamed or dropped.
  • Cannot be taken offline while the database is OPEN (only possible in MOUNT stage).
  • Can technically store user objects, but this is not recommended.
SYSAUX Tablespace (introduced in 10g)
  • Stores data for auxiliary/system-related applications and database performance information.
  • Automatically created when the database is built using DBCA.
  • Requires a higher level of security.
  • Cannot be dropped or renamed.
  • User objects should NOT be stored here — reserved for system-specific data only.
  • It is a permanent tablespace.
NON-SYSTEM Tablespace (User Objects)
  • Used to store application/user data in tables and indexes — required for normal day-to-day operations.
  • Gives the DBA flexibility to separate data based on backup requirements.
  • Allows separation of dynamic and static data to enable database tuning.
  • Reduces contention for I/O path access (to/from memory & disk).
Quick DBA Commands — Permanent Tablespace
SQL
-- Create a tablespace
CREATE TABLESPACE sdbt
DATAFILE '/u01/app/oracle/oradata/mac/sdbt01.dbf' SIZE 10M;

SELECT tablespace_name, file_name FROM dba_data_files;
SELECT tablespace_name FROM dba_tablespaces;

-- Add a datafile
ALTER TABLESPACE tesdb
ADD DATAFILE '/u01/app/oracle/oradata/tesdb02.dbf' SIZE 200M;

-- Resize a datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/tesdb02.dbf' RESIZE 100M;
SELECT tablespace_name, file_name, bytes/1024/1024 FROM dba_data_files;

-- Rename a tablespace
ALTER TABLESPACE tesdb RENAME TO tes;

-- Drop a tablespace
DROP TABLESPACE tesdb;  -- Logical drop only
DROP TABLESPACE tesdb INCLUDING CONTENTS AND DATAFILES;  -- Logical + physical drop

-- Online / offline and read only / read write
ALTER TABLESPACE tesdb READ ONLY;   -- blocks insert/update/delete
ALTER TABLESPACE tesdb READ WRITE;  -- back online
ALTER TABLESPACE tesdb OFFLINE;
ALTER TABLESPACE tesdb ONLINE;
SELECT tablespace_name, status FROM dba_tablespaces;

-- Put tablespace in hot backup mode
ALTER TABLESPACE tesdb BEGIN BACKUP;
SELECT * FROM v$backup;
ALTER TABLESPACE tesdb END BACKUP;

-- View tablespace metadata (DDL)
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

-- Create a Bigfile tablespace
CREATE BIGFILE TABLESPACE test
DATAFILE '/u01/app/oracle/oradata/tesdb01.dbf' SIZE 10M;
SELECT tablespace_name, bigfile FROM dba_tablespaces;

-- Set database default file type
ALTER DATABASE SET DEFAULT SMALLFILE TABLESPACE;
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

-- Assign at USER level
CREATE USER sdbt IDENTIFIED BY sdbt DEFAULT TABLESPACE test;
CREATE USER u6 IDENTIFIED BY u6 DEFAULT TABLESPACE vis QUOTA 5M ON vis;
ALTER USER sdbt DEFAULT TABLESPACE tesdb;
ALTER USER u1 QUOTA 10M ON tesdb;
ALTER USER raja QUOTA UNLIMITED ON tesdb;
SELECT username, default_tablespace FROM dba_users;
SELECT * FROM dba_ts_quotas WHERE username = 'U6';

-- Assign at TABLE level
CREATE TABLE t1 (id INT, name VARCHAR2(20)) TABLESPACE tesdb;
ALTER TABLE t2 MOVE TABLESPACE tesdb;
SELECT table_name, tablespace_name FROM user_tables;

-- Assign at DATABASE level
ALTER DATABASE DEFAULT TABLESPACE tesdb;
SELECT * FROM database_properties;

-- Move / rename a datafile (offline, copy at OS, rename, recover, online)
ALTER DATABASE DATAFILE '/u01/.../bot01.dbf' OFFLINE;
-- OS level: cp tesdb01.dbf /u01/app/oracle/oradata/tesdb011.dbf
ALTER DATABASE RENAME FILE '/u01/.../tesdb01.dbf' TO '/u01/.../tesdb11.dbf';
RECOVER DATAFILE '/u01/.../tesdb11.dbf';
ALTER DATABASE DATAFILE '/u01/.../tesdb11.dbf' ONLINE;

2) Undo Tablespace

Before Oracle 8i → managed using Rollback Segments. From Oracle 9i onward → managed using Undo Management (Automatic Undo Management).

  • Maintains information used to roll back / undo changes made to the database.
  • Stores records of transaction actions, primarily before they are committed.
Purpose of Undo Data
  • Roll back transactions when a ROLLBACK statement is issued.
  • Used during database recovery.
  • Provides read consistency for other sessions while a transaction is in progress.
  • Enables analyzing data as of an earlier point in time using Oracle Flashback Query.
  • Helps recover from logical corruptions using Oracle Flashback features.
Undo Segment Status
  • CURRENT — actively being used by an active transaction.
  • ACTIVE — contains undo data required by an active transaction.
  • INACTIVE — undo data not tied to any active transaction, but still within retention.
  • EXPIRED / UNEXPIRED — undo data has crossed (expired) or is still within (unexpired) the undo retention period and is eligible for overwrite.
Oracle VersionUndo Segment Status Flow
10gEXPIRED → INACTIVE → ACTIVE → CURRENT
11gINACTIVE → ACTIVE → CURRENT
12cACTIVE → CURRENT
13cCURRENT
Quick DBA Commands — Undo Tablespace
SQL
SHOW PARAMETER undo;

-- Create an undo tablespace
CREATE UNDO TABLESPACE undo
DATAFILE '/u01/app/oracle/oradata/dev/undo01.dbf' SIZE 10M;

SELECT tablespace_name FROM dba_tablespaces;
SELECT tablespace_name, file_name, file_id FROM dba_data_files;

-- Useful views
dba_tablespaces | dba_data_files | v$datafile
dba_free_space  | dba_temp_files

-- Switch active undo tablespace & set retention
ALTER SYSTEM SET undo_tablespace = 'undo';
ALTER SYSTEM SET undo_retention = 1000;

-- Resize / autoextend / offline / online the undo datafile
ALTER DATABASE DATAFILE '/u01/.../undo01.dbf' RESIZE 20M;
ALTER DATABASE DATAFILE '/u01/.../undo01.dbf' AUTOEXTEND ON;
ALTER DATABASE DATAFILE '/u01/.../undo01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/u01/.../undo01.dbf' ONLINE;

-- Drop the undo tablespace
DROP TABLESPACE undo INCLUDING CONTENTS AND DATAFILES;

-- Retention guarantee (never overwrite unexpired undo, even if it stalls DML)
ALTER TABLESPACE undotb RETENTION GUARANTEE;
ALTER TABLESPACE undo RETENTION NOGUARANTEE;
SELECT tablespace_name, contents, retention FROM dba_tablespaces;

-- Undo statistics (usage, transaction count, tuning)
SELECT * FROM v$undostat;

3) Temporary Tablespace

  • Used to manage space required for sort operations.
  • Ensures SQL queries are less likely to run out of space during large sort/hash operations.
  • Oracle stores temporary tables, temporary indexes, temporary B-trees, and temporary LOBs in temporary tablespaces.
  • Made up of tempfiles (not regular datafiles) — content is not permanent and not backed up.
Quick DBA Commands — Temporary Tablespace
SQL
-- Create a temporary tablespace
CREATE TEMPORARY TABLESPACE tmp01
TEMPFILE '/home/oracle/dev/oradata/data/tmp01.dbf' SIZE 100M;

-- Set as the database default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs;

-- Check tempfile details
SELECT file_id, tablespace_name, bytes/1024/1024 AS MB FROM dba_temp_files;

-- Check current default temp tablespace
SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

-- Autoextend a tempfile
ALTER DATABASE TEMPFILE '/home/oracle/dev/oradata/data/tmp01.dbf' AUTOEXTEND ON;
SELECT file_name, autoextensible FROM dba_temp_files
WHERE tablespace_name = 'TMP01';

-- Resize a tempfile (by file id)
ALTER DATABASE TEMPFILE 2 RESIZE 120M;

-- Check free space in a temp tablespace
SELECT free_space FROM dba_temp_free_space
WHERE tablespace_name = 'TEMP';

Easy Memory

TABLESPACE
|
+-- Datafile --> Segment --> Extent --> Block
|
+-- By Data Type
|     +-- PERMANENT --> SYSTEM / SYSAUX / Non-System (tables, indexes)
|     +-- UNDO       --> Before-image (rollback, read consistency, flashback)
|     +-- TEMPORARY  --> Tempfile (sort, order by, group by, distinct)
|
+-- By Size
      +-- Smallfile --> many datafiles, up to 128 GB each
      +-- Bigfile   --> single datafile, up to 128 TB