Tablespace management is one of those DBA tasks that seems simple until a production tablespace fills up at 2am. This article covers monitoring, autoextend configuration, growth forecasting, and the right way to handle tablespace emergencies.

Tablespace Fundamentals

A tablespace is a logical storage container. Each tablespace consists of one or more physical datafiles. Objects (tables, indexes, LOBs) are assigned to tablespaces, and Oracle allocates extents from the tablespace's datafiles as objects grow.

-- Current tablespace usage overview
SELECT t.tablespace_name,
       ROUND(t.bytes/1024/1024/1024, 2) total_gb,
       ROUND((t.bytes - f.bytes)/1024/1024/1024, 2) used_gb,
       ROUND(f.bytes/1024/1024/1024, 2) free_gb,
       ROUND((t.bytes - f.bytes)/t.bytes * 100, 1) pct_used,
       t.status,
       t.contents,
       t.bigfile
FROM (
  SELECT tablespace_name, SUM(bytes) bytes, status, contents, bigfile
  FROM dba_tablespaces t
  JOIN dba_data_files d USING (tablespace_name)
  GROUP BY tablespace_name, status, contents, bigfile
) t
JOIN (
  SELECT tablespace_name, SUM(bytes) bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
ORDER BY pct_used DESC;

Autoextend — When to Use It and When Not To

Autoextend allows datafiles to grow automatically when a tablespace fills. It prevents ORA-01653 (unable to extend table) errors but can also silently consume all available disk space.

-- Check current autoextend settings
SELECT file_name, tablespace_name,
       ROUND(bytes/1024/1024/1024, 2) current_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024, 2) max_gb,
       ROUND(increment_by * 8192/1024/1024, 0) increment_mb
FROM dba_data_files
ORDER BY tablespace_name;

Enable autoextend with a sensible maximum

Never enable autoextend without a MAXSIZE — an unbounded autoextend file can fill the filesystem:

-- Enable autoextend with 100MB increments, max 50GB
ALTER DATABASE DATAFILE '/u01/oradata/PROD/users01.dbf'
  AUTOEXTEND ON NEXT 100M MAXSIZE 50G;

-- Add a new datafile with autoextend to a tablespace
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/PROD/users02.dbf'
  SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

When to disable autoextend

Disable autoextend for:

-- Disable autoextend
ALTER DATABASE DATAFILE '/u01/oradata/PROD/undotbs01.dbf' AUTOEXTEND OFF;

Growth Forecasting

Don't wait for tablespaces to fill — forecast growth from historical data:

-- Tablespace growth over the last 30 days (from AWR)
SELECT tablespace_name,
       MIN(used_gb) min_used_gb,
       MAX(used_gb) max_used_gb,
       MAX(used_gb) - MIN(used_gb) growth_gb_30d,
       ROUND((MAX(used_gb) - MIN(used_gb)) / 30, 2) avg_daily_growth_gb
FROM (
  SELECT h.snap_id,
         t.tablespace_name,
         ROUND((t.tablespace_size - t.tablespace_usedsize) * 8192 / 1024/1024/1024, 2) free_gb,
         ROUND(t.tablespace_usedsize * 8192 / 1024/1024/1024, 2) used_gb
  FROM dba_hist_tbspc_space_usage t
  JOIN dba_hist_snapshot h ON t.snap_id = h.snap_id
  WHERE h.begin_interval_time > SYSDATE - 30
)
GROUP BY tablespace_name
ORDER BY avg_daily_growth_gb DESC;

-- Days until full (based on 30-day growth rate)
SELECT tablespace_name,
       ROUND(free_gb / NULLIF(avg_daily_growth_gb, 0)) days_until_full
FROM (
  -- combine the above two queries
  SELECT t.tablespace_name,
         f.free_gb,
         g.avg_daily_growth_gb
  FROM dba_tablespaces t
  JOIN (...) f ON t.tablespace_name = f.tablespace_name
  JOIN (...) g ON t.tablespace_name = g.tablespace_name
)
WHERE days_until_full < 90
ORDER BY days_until_full;

Handling a Full Tablespace Emergency

ORA-01653: unable to extend table X.Y by Z in tablespace T

This is a production emergency — applications start failing immediately. Fix it fast:

Option 1: Add a datafile (fastest, permanent)

-- Check current datafiles
SELECT file_name, ROUND(bytes/1024/1024/1024,2) size_gb
FROM dba_data_files
WHERE tablespace_name = 'USERS';

-- Add a new datafile
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/PROD/users03.dbf'
  SIZE 10G AUTOEXTEND ON NEXT 500M MAXSIZE 50G;

Option 2: Resize an existing datafile

-- Increase an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/PROD/users01.dbf' RESIZE 20G;

Option 3: Enable autoextend on existing file (temporary fix)

-- Quick fix while you arrange permanent solution
ALTER DATABASE DATAFILE '/u01/oradata/PROD/users01.dbf'
  AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

Option 4: Reclaim space (if possible)

-- Find biggest objects in the tablespace
SELECT owner, segment_name, segment_type,
       ROUND(bytes/1024/1024/1024, 2) size_gb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Shrink a table (reclaims space below HWM)
ALTER TABLE schema.big_table ENABLE ROW MOVEMENT;
ALTER TABLE schema.big_table SHRINK SPACE CASCADE;
ALTER TABLE schema.big_table DISABLE ROW MOVEMENT;

-- Rebuild an index (reclaims fragmented space)
ALTER INDEX schema.big_index REBUILD ONLINE;

TEMP Tablespace Management

TEMP fills up when sort operations, hash joins, and global temporary tables exceed PGA memory limits. A full TEMP causes ORA-01652.

-- Current TEMP usage
SELECT tablespace_name,
       ROUND(tablespace_size * 8192/1024/1024/1024, 2) total_gb,
       ROUND(allocated_space * 8192/1024/1024/1024, 2) allocated_gb,
       ROUND(free_space * 8192/1024/1024/1024, 2) free_gb
FROM gv$temp_space_header
ORDER BY inst_id;

-- Who is using TEMP right now
SELECT s.sid, s.serial#, s.username, s.program,
       ROUND(u.blocks * 8192/1024/1024, 0) temp_mb,
       s.sql_id
FROM v$tempseg_usage u
JOIN v$session s ON u.session_id = s.sid
ORDER BY u.blocks DESC;

-- Add space to TEMP
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/PROD/temp02.dbf'
  SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- Shrink TEMP (reclaims space not actively used)
ALTER TABLESPACE temp SHRINK SPACE KEEP 2G;

UNDO Tablespace Sizing

UNDO size depends on undo_retention (how long Oracle keeps old versions of data for read consistency and Flashback queries) and your transaction rate.

-- Current undo usage
SELECT status, COUNT(*) segments, SUM(blocks)*8192/1024/1024 mb
FROM dba_undo_extents
GROUP BY status;

-- Calculate required UNDO size for current undo_retention
SELECT d.value retention_seconds,
       r.undoblks * 8192 / 1024 / 1024 undo_mb_needed
FROM v$parameter d,
     (SELECT SUM(undoblks) undoblks FROM v$undostat
      WHERE end_time > SYSDATE - 1/24) r
WHERE d.name = 'undo_retention';

-- Undo advisor (requires Diagnostics Pack)
SELECT d.name, d.value
FROM v$undostat u, v$parameter d
WHERE d.name = 'undo_retention'
AND ROWNUM = 1;

Bigfile Tablespaces

For very large databases, bigfile tablespaces use a single datafile that can be up to 128TB (with 32K block size). Simpler management but no striping across multiple files:

-- Create a bigfile tablespace
CREATE BIGFILE TABLESPACE big_data
  DATAFILE '/u01/oradata/PROD/big_data01.dbf' SIZE 100G
  AUTOEXTEND ON NEXT 10G MAXSIZE 10T;

-- Resize a bigfile tablespace (simpler than regular)
ALTER TABLESPACE big_data RESIZE 200G;

Monitoring Alerts

Set up proactive monitoring to alert before tablespaces fill:

-- Oracle's built-in threshold alerts (server-generated alerts)
-- Check current thresholds
SELECT object_name, metrics_name, warning_value, critical_value
FROM dba_thresholds
WHERE metrics_name = 'Tablespace Space Used (%)';

-- Set alert thresholds
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
  metrics_id        => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
  warning_operator  => DBMS_SERVER_ALERT.OPERATOR_GE,
  warning_value     => '80',
  critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
  critical_value    => '90',
  observation_period => 1,
  consecutive_occurrences => 1,
  instance_name     => NULL,
  object_type       => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
  object_name       => 'USERS'
);

TuneVault monitors all tablespace usage continuously — alerting when any tablespace exceeds 80% and showing growth trends so you can add capacity before applications are impacted rather than after.