How to Create a Tablespace in Oracle - Step by Step

How to Create a Tablespace in Oracle

A tablespace is Oracle’s logical storage container — every segment (table, index, LOB) lives in one. Before creating objects, you need a correctly sized tablespace with autoextend configured. This guide covers every tablespace type with production-ready SQL.

Prerequisites

You need the CREATE TABLESPACE system privilege, typically granted to DBAs. You also need:

• An identified datafile location with sufficient disk space
• Knowledge of your storage layout (ASM disk groups or filesystem paths)

-- Verify you have the privilege
SELECT privilege
FROM session_privs
WHERE privilege = 'CREATE TABLESPACE';

-- Check existing tablespaces
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
ORDER BY tablespace_name;

-- Check available space on ASM or filesystem
SELECT name, total_mb, free_mb, ROUND(free_mb/total_mb*100,1) AS free_pct
FROM v$asm_diskgroup;

Step 1: Create a Basic Permanent Tablespace

This is the most common operation — a locally managed tablespace with autoextend enabled.

-- Basic permanent tablespace (filesystem)
CREATE TABLESPACE app_data
  DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- Verify creation
SELECT tablespace_name, status, contents, extent_management,
       segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

Using ASM (Recommended for Production)

-- Permanent tablespace on ASM
CREATE TABLESPACE app_data
  DATAFILE '+DATA/ORCL/DATAFILE/app_data01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 20G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

Step 2: Create a Temporary Tablespace

Every database needs a default temporary tablespace for sort operations. Use TEMPFILE instead of DATAFILE.

-- Create a temporary tablespace
CREATE TEMPORARY TABLESPACE app_temp
  TEMPFILE '/u01/oradata/ORCL/app_temp01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 512M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Set as the default temporary tablespace for new users
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE app_temp;

-- Verify
SELECT tablespace_name, contents, status
FROM dba_tablespaces
WHERE contents = 'TEMPORARY';

Step 3: Create an Undo Tablespace

Undo tablespaces hold rollback data for read consistency and flashback operations.

-- Create undo tablespace
CREATE UNDO TABLESPACE undo_new
  DATAFILE '/u01/oradata/ORCL/undo_new01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

-- Switch the active undo tablespace (online, no restart needed)
ALTER SYSTEM SET undo_tablespace = 'UNDO_NEW' SCOPE=BOTH;

-- Verify the switch took effect
SHOW PARAMETER undo_tablespace;

Step 4: Create a Bigfile Tablespace

Bigfile tablespaces contain a single, very large datafile (up to 128TB). Ideal for large data warehouses.

-- Create bigfile tablespace
CREATE BIGFILE TABLESPACE dw_data
  DATAFILE '+DATA/ORCL/DATAFILE/dw_data01.dbf' SIZE 50G
  AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- Resize a bigfile tablespace (single command, no datafile reference needed)
ALTER TABLESPACE dw_data RESIZE 100G;

-- Check if tablespace is bigfile
SELECT tablespace_name, bigfile
FROM dba_tablespaces
WHERE tablespace_name = 'DW_DATA';

Step 5: Create an Encrypted Tablespace (TDE)

Transparent Data Encryption (TDE) encrypts data at rest — required for many compliance frameworks.

-- First, ensure the wallet is open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
  IDENTIFIED BY "WalletPassword123"
  CONTAINER = ALL;

-- Create encrypted tablespace
CREATE TABLESPACE secure_data
  DATAFILE '/u01/oradata/ORCL/secure_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 5G
  ENCRYPTION USING AES256
  DEFAULT STORAGE (ENCRYPT);

-- Verify encryption
SELECT tablespace_name, encrypted
FROM dba_tablespaces
WHERE tablespace_name = 'SECURE_DATA';

Advanced Examples

Uniform Extent Size (for OLTP workloads)

-- Uniform extent size — reduces fragmentation for similar-sized objects
CREATE TABLESPACE oltp_idx
  DATAFILE '/u01/oradata/ORCL/oltp_idx01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 512M MAXSIZE 20G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M
  SEGMENT SPACE MANAGEMENT AUTO;

Multiple Datafiles at Creation

-- Create tablespace with multiple datafiles for I/O distribution
CREATE TABLESPACE reporting
  DATAFILE '/u01/oradata/ORCL/rpt01.dbf' SIZE 5G,
           '/u02/oradata/ORCL/rpt02.dbf' SIZE 5G,
           '/u03/oradata/ORCL/rpt03.dbf' SIZE 5G
  AUTOEXTEND OFF
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

Read-Only Tablespace

-- Create tablespace, load data, then make read-only (for archival)
CREATE TABLESPACE archive_2023
  DATAFILE '/u01/oradata/ORCL/archive_2023_01.dbf' SIZE 10G
  AUTOEXTEND OFF;

-- After loading data:
ALTER TABLESPACE archive_2023 READ ONLY;

Common Mistakes and Pitfalls

Using DICTIONARY extent management — Always use EXTENT MANAGEMENT LOCAL. Dictionary-managed tablespaces are obsolete and cause contention on Oracle 10g+.

No MAXSIZE limit with AUTOEXTEND — Setting AUTOEXTEND ON without MAXSIZE allows a tablespace to fill an entire disk. Always set a sensible limit.

Wrong sizing for temp tablespace — Temp tablespaces need enough space for the largest parallel sort operation, not just average usage. Underprovision and you get ORA-01652.

Creating tablespace as SYSTEM user — Objects in SYSTEM or SYSAUX tablespaces cause problems. Always use dedicated application tablespaces.

Using MANUAL segment space management — SEGMENT SPACE MANAGEMENT MANUAL uses freelists and causes contention. Always use AUTO (bitmap-based).

Verification Queries

-- Confirm tablespace was created with correct settings
SELECT
  t.tablespace_name,
  t.status,
  t.contents,
  t.extent_management,
  t.segment_space_management,
  t.bigfile,
  t.encrypted,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
WHERE t.tablespace_name = 'APP_DATA'
GROUP BY t.tablespace_name, t.status, t.contents,
         t.extent_management, t.segment_space_management,
         t.bigfile, t.encrypted;

-- Check autoextend settings for all datafiles
SELECT
  file_name,
  ROUND(bytes/1024/1024, 0)    AS current_mb,
  autoextensible,
  ROUND(increment_by * 8192/1024/1024, 0) AS next_mb,
  ROUND(maxbytes/1024/1024/1024, 2)        AS max_gb
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA';

-- Confirm free space after creation
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1024/1024, 0) AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'APP_DATA'
GROUP BY tablespace_name;

Related Topics

• How to Add a Datafile - Extend an existing tablespace
• How to Monitor Tablespace Usage - Track free space and growth
• How to Switch Undo Tablespace - Manage undo online
• Tablespace Scripts - Comprehensive DBA scripts