Introduction to Tablespace Management (Oracle 23c/23ai, Multitenant)

In Oracle 23c/23ai, tablespace management is designed for multitenant-by-default architectures. A CDB hosts one or more PDBs, and most storage and space-governance tasks should be scoped and automated at the PDB level. This lesson orients you to modern practices: locally managed tablespaces, bigfile design, encryption, online operations, and cloud-ready administration.

Learning Objectives

• Understand modern tablespace types and defaults (LMT, ASSM, bigfile).
• Design tablespaces for PDB isolation, quotas, and growth control.
• Use online, low-downtime operations (resize, move, encrypt).
• Apply ILM/ADO and transportable/cross-platform options for lifecycle moves.

Modern Defaults annd Concepts

• Locally Managed Tablespaces (LMT): Space tracked by bitmaps inside each tablespace (no data dictionary contention). ASSM (Automatic Segment Space Management) is standard for efficient free-space management.
• Bigfile Tablespaces: One very large datafile per tablespace simplifies management and works well with ASM/OCI block volumes and thin provisioning.
• Multitenant Scope: Create and govern tablespaces inside each PDB for isolation (quotas, temp groups, undo per PDB option).
• Encryption by Default: Tablespace-level TDE is common practice; combine with wallet/KMS for compliance.
• Online Maintenance: Resize/move/encrypt datafiles online (where supported) to minimize downtime.

Creating Core Tablespaces (CDB vs PDB)

Create locally inside the target PDB unless you are provisioning common/system storage in the root.

-- In the root, switch into the PDB for tenant-scoped storage
ALTER SESSION SET CONTAINER = salespdb;

-- OLTP tablespace: bigfile + ASSM + autoextend with guardrails
CREATE BIGFILE TABLESPACE ts_oltp
  DATAFILE '+DATA' SIZE 20G
  AUTOEXTEND ON NEXT 1G MAXSIZE 200G
  SEGMENT SPACE MANAGEMENT AUTO
  ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

-- Index tablespace (optional separation)
CREATE BIGFILE TABLESPACE ts_idx
  DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G
  SEGMENT SPACE MANAGEMENT AUTO;

-- TEMP: use a temp group to scale parallel/ETL
CREATE TEMPORARY TABLESPACE temp_pdb
  TEMPFILE '+DATA' SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE 64G;

ALTER SESSION SET CONTAINER = salespdb;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_pdb;

Growth Controls and Quotas

Use autoextend with MAXSIZE and PDB/user quotas to prevent runaway growth.

-- Cap the tablespace at the PDB level by design (MAXSIZE above)
-- Also cap user/schema storage
ALTER USER app_user QUOTA 50G ON ts_oltp;
ALTER USER app_user QUOTA 20G ON ts_idx;

Online Operations (Low Downtime)

• Resize datafiles online to respond to growth.
• Move datafiles online (ASM/OCI storage) to rebalance or change tiers.
• Encrypt in place where supported to meet policy without long outages.

-- Resize (up or down, where allowed)
ALTER DATABASE DATAFILE '+DATA/SALES/ts_oltp.256.12345' RESIZE 25G;

-- Add second datafile (smallfile example) or rely on bigfile autoextend
ALTER TABLESPACE ts_oltp ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G;

-- Move a datafile online (example path is illustrative)
ALTER DATABASE MOVE DATAFILE '+DATA/SALES/ts_idx.257.12346' TO '+DATAFAST/SALES/ts_idx.001';

Read-Only, Transportable and Cross-Platform

For archival or bulk transfer, use read-only and transportable tablespaces; for platform changes, enable cross-platform transport with RMAN conversion.

-- Make read-only for stable extracts
ALTER TABLESPACE ts_warehouse READ ONLY;

-- Transport metadata (dp) + datafiles (OS/ASM copy); for x-platform, convert via RMAN
-- RMAN CROSS PLATFORM (example)
RMAN> CONVERT TABLESPACE ts_warehouse TO PLATFORM 'Linux x86 64-bit'
      FORMAT '/stage/ts_warehouse_%U';

ILM / ADO (Information Lifecycle Management)

Use Automatic Data Optimization (ADO) policies to compress, move, or set read-only based on age or access, keeping hot data on fast storage and cold data compressed.

-- Example: compress segment after 30 days without modification
ALTER TABLE sales.transactions
  ILM ADD POLICY ROW STORE COMPRESS ADVANCED
  SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

Temporary and Undo Strategy

• Temp: Use tablespace groups for concurrent ETL/reporting; ensure adequate tempfile I/O bandwidth.
• Undo: For strong isolation, consider local undo per PDB; size for flashback/query workload.

-- Temp group for PDB (multiple temp tablespaces can join a group)
ALTER TABLESPACE temp_pdb TABLESPACE GROUP tempgrp_sales;

Monitoring and Guardrails

• Track free/used bytes at PDB + tablespace levels; alert on % thresholds.
• Use AWR/ASH for segment hotspots; move heavy segments to dedicated tablespaces if needed.
• Back up tablespaces regularly (RMAN), and validate restores for PITR scenarios.

-- Quick visibility into tablespace usage (example query)
SELECT tablespace_name, ROUND(used_space*8/1024) used_mb,
       ROUND(tablespace_size*8/1024) size_mb
FROM   v$tablespace t
JOIN   v$tsm_storage s ON t.ts# = s.ts# 
ORDER  BY used_mb DESC;

Design Checklist (Sanity Check)

• LMT + ASSM everywhere; prefer bigfile for simplicity with ASM/OCI.
• Encrypt new tablespaces (TDE) by policy; confirm wallet/KMS integration.
• Autoextend with MAXSIZE; set user/PDB quotas; monitor at 70/85/95% thresholds.
• Keep OLTP/IDX/TEMP separated for manageability; consider dedicated TS for large CLOB/BLOB.
• Adopt ILM/ADO for cold data; use transportable/x-platform for bulk moves.
• Exploit online maintenance to minimize downtime; script repeatable tasks.

Next lesson: Creating, resizing, encrypting, and monitoring tablespaces with scripts and guardrails for Oracle 23c/23ai in multitenant environments.