The Two Modes of Datafile Sizing

Every Oracle tablespace is backed by datafiles. Each datafile is either:

Most production databases use a mix of both. Knowing when to use each is the first decision in tablespace management.

When to Use AUTOEXTEND

Autoextend makes sense for:

Autoextend is risky for:

When to Use Fixed Size with Alerts

Fixed-size datafiles force you to take an explicit action before a tablespace grows. This is the right model for application data because it catches problems:

The tradeoff: you need active monitoring and the discipline to pre-allocate space before it is needed.

Core Monitoring Queries

Current Tablespace Usage

SELECT
    t.tablespace_name,
    t.contents,
    ROUND(NVL(f.free_mb, 0), 2)             free_mb,
    ROUND(t.total_mb, 2)                    total_mb,
    ROUND((1 - NVL(f.free_mb,0)/t.total_mb)*100, 1) pct_used,
    ROUND(NVL(mx.maxsize_mb, t.total_mb), 2) maxsize_mb,
    CASE
      WHEN (1 - NVL(f.free_mb,0)/t.total_mb)*100 > 90 THEN 'CRITICAL'
      WHEN (1 - NVL(f.free_mb,0)/t.total_mb)*100 > 80 THEN 'WARNING'
      ELSE 'OK'
    END status
FROM
    (SELECT tablespace_name, contents,
            SUM(bytes)/1048576 total_mb
     FROM dba_data_files GROUP BY tablespace_name, contents) t
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1048576 free_mb
     FROM dba_free_space GROUP BY tablespace_name) f
    ON t.tablespace_name = f.tablespace_name
LEFT JOIN
    (SELECT tablespace_name,
            SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END)/1048576 maxsize_mb
     FROM dba_data_files GROUP BY tablespace_name) mx
    ON t.tablespace_name = mx.tablespace_name
ORDER BY pct_used DESC;

This query accounts for autoextend: if a datafile has MAXSIZE set, it uses that as the ceiling rather than the current allocation. This gives you a more accurate "how much headroom do I really have?" answer.

UNDO and TEMP (Use Different Views)

UNDO and TEMP tablespaces are not covered by dba_free_space:

-- UNDO usage
SELECT
    d.tablespace_name,
    ROUND(SUM(d.bytes)/1e9, 2) allocated_gb,
    ROUND(SUM(u.bytes)/1e9, 2) used_gb,
    ROUND(SUM(u.bytes)*100/NULLIF(SUM(d.bytes),0), 1) pct_used
FROM dba_data_files d
JOIN v$undostat u ON 1=1  -- cross-join trick; v$undostat shows current undo usage
WHERE d.tablespace_name = (SELECT value FROM v$parameter WHERE name='undo_tablespace')
GROUP BY d.tablespace_name;

-- Simpler UNDO check via v$undostat (last 1440 minutes = 24 hours of stats)
SELECT
    TO_CHAR(begin_time,'HH24:MI') period,
    undoblks,
    txncount,
    maxconcurrency,
    ROUND(undoblks * 8192 / 1e9, 3) undo_gb_per_period
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 12 ROWS ONLY;


-- TEMP usage (currently used)
SELECT
    t.tablespace_name,
    ROUND(SUM(t.bytes)/1e9, 2) total_gb,
    ROUND(NVL(SUM(u.bytes_used)/1e9, 0), 2) used_gb,
    ROUND(NVL(SUM(u.bytes_used)*100/SUM(t.bytes), 0), 1) pct_used
FROM dba_temp_files t
LEFT JOIN v$temp_extent_pool u ON t.file_id = u.file_id
GROUP BY t.tablespace_name;

Growth Rate Analysis

Knowing current usage tells you today's problem. Growth rate tells you next month's.

-- Tablespace growth over the last 7 days (requires AWR — Diagnostics Pack license)
SELECT
    tablespace_name,
    TO_CHAR(snap_date, 'YYYY-MM-DD') snap_date,
    ROUND(allocated_mb, 1) allocated_mb,
    ROUND(used_mb, 1) used_mb,
    ROUND(used_mb - LAG(used_mb, 1) OVER (PARTITION BY tablespace_name ORDER BY snap_date), 1) daily_growth_mb
FROM (
    SELECT
        s.tablespace_name,
        TRUNC(sn.end_interval_time) snap_date,
        MAX(s.tablespace_size * 8 / 1024) allocated_mb,
        MAX(s.tablespace_used_size * 8 / 1024) used_mb
    FROM dba_hist_tbspc_space_usage s
    JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id
    WHERE sn.end_interval_time >= SYSDATE - 7
    GROUP BY s.tablespace_name, TRUNC(sn.end_interval_time)
)
ORDER BY tablespace_name, snap_date;

For non-AWR environments, log snapshots daily using a custom table:

-- Create a simple tablespace history log
CREATE TABLE dba_ts_history AS
SELECT SYSDATE snap_time, tablespace_name, SUM(bytes)/1048576 used_mb
FROM dba_segments
GROUP BY tablespace_name;

-- Schedule via DBMS_SCHEDULER to run nightly
-- Then query growth trend:
SELECT tablespace_name,
       MAX(used_mb) - MIN(used_mb) growth_mb_over_period,
       COUNT(*) days_sampled
FROM dba_ts_history
WHERE snap_time >= SYSDATE - 30
GROUP BY tablespace_name
ORDER BY growth_mb_over_period DESC;

Adding Space

When you need to extend a tablespace:

-- Option 1: Add a new datafile
ALTER TABLESPACE APP_DATA
  ADD DATAFILE '/u02/oradata/PRODDB/app_data02.dbf'
  SIZE 50G AUTOEXTEND OFF;

-- Option 2: Resize an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/PRODDB/app_data01.dbf' RESIZE 100G;


-- Option 3: Enable autoextend on existing datafile (carefully)
ALTER DATABASE DATAFILE '/u01/oradata/PRODDB/app_data01.dbf'
  AUTOEXTEND ON MAXSIZE 200G;


-- Find current datafiles and their sizes
SELECT file_name, bytes/1073741824 size_gb, maxbytes/1073741824 maxsize_gb, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id;

Bigfile Tablespaces

Bigfile tablespaces use a single datafile instead of many smallfiles. Benefits:


Drawbacks:

Use bigfile for ASM environments (ASM handles striping) or when you are already using Oracle Managed Files. Avoid bigfile on filesystems where your backup software can parallelize by file.

-- Create a bigfile tablespace
CREATE BIGFILE TABLESPACE WAREHOUSE_DATA
  DATAFILE '/u04/oradata/PRODDB/warehouse01.dbf' SIZE 5T
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  SEGMENT SPACE MANAGEMENT AUTO;

-- Check if a tablespace is bigfile
SELECT tablespace_name, bigfile FROM dba_tablespaces;

Setting Up Proactive Alerts

The best way to avoid tablespace emergencies is to alert before you hit 80%. Oracle provides built-in thresholds via the Server Alert system:

-- Set custom warning/critical thresholds per tablespace
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 => 30,
  consecutive_occurrences => 1,
  instance_name    => NULL,
  object_type      => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
  object_name      => 'APP_DATA'
);

-- View current thresholds
SELECT object_name, metrics_name, warning_value, critical_value
FROM dba_thresholds
WHERE object_type = 'TABLESPACE';

For email alerts via DBMS_ALERT or an external script:

#!/bin/bash

tablespace_alert.sh — run from cron every 30 minutes

ALERT_THRESHOLD=80

sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF HEADING OFF
SELECT tablespace_name || '|' || ROUND((1-f.free_mb/t.total_mb)*100,1)
FROM (SELECT tablespace_name, SUM(bytes)/1048576 total_mb FROM dba_data_files GROUP BY tablespace_name) t
LEFT JOIN (SELECT tablespace_name, SUM(bytes)/1048576 free_mb FROM dba_free_space GROUP BY tablespace_name) f
USING (tablespace_name)
WHERE (1-NVL(f.free_mb,0)/t.total_mb)*100 > $ALERT_THRESHOLD;
EXIT;
EOF

UNDO Sizing: A Practical Formula

Undersized UNDO causes ORA-01555 (snapshot too old) during long queries. Oversized UNDO wastes disk. The correct UNDO size depends on:

-- Calculate recommended UNDO size
SELECT
    MAX(undoblks) * 8192 / 1e9                 peak_undo_gb,
    (SELECT value FROM v$parameter WHERE name='undo_retention') undo_retention_s,
    MAX(undoblks)  8192 / 1e9 
    (SELECT value FROM v$parameter WHERE name='undo_retention') / 1800 recommended_size_gb
FROM v$undostat;

The formula: recommended_undo_size = (undo_blocks_per_second × block_size × undo_retention_seconds). Add 20% headroom.

TuneVault monitors tablespace usage across all your Oracle connections, alerting you when any tablespace exceeds your configured threshold — with 30-day growth trend analysis to predict when you will run out before it becomes an emergency.