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 Version | Undo Segment Status Flow |
|---|---|
| 10g | EXPIRED → INACTIVE → ACTIVE → CURRENT |
| 11g | INACTIVE → ACTIVE → CURRENT |
| 12c | ACTIVE → CURRENT |
| 13c | CURRENT |
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