Skip to content
DBAExperts
Oracle Operations

How to monitor tablespaces and their growth in Oracle

A full tablespace stops writes and can take down the application. Monitoring space in time prevents unplanned outages.

  1. 1

    Review usage by tablespace

    Compare allocated space against free space to see the percentage used for each tablespace.

    SELECT df.tablespace_name,
  ROUND(df.total/1024/1024) mb_total,
  ROUND((df.total - NVL(fs.libre,0))/1024/1024) mb_usado,
  ROUND((df.total - NVL(fs.libre,0))/df.total*100,1) pct_uso
FROM (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN (SELECT tablespace_name, SUM(bytes) libre FROM dba_free_space GROUP BY tablespace_name) fs
  ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_uso DESC;
  2. 2

    Check datafile autoextend

    A datafile without autoextend, or with a low maxbytes, fills up even when the disk has space.

    SELECT tablespace_name, file_name, autoextensible, maxbytes
FROM dba_data_files;
  3. 3

    Grow it when needed

    Add a datafile or raise the maximum. Confirm first that the disk has physical space.

    ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/users02.dbf' SIZE 1G AUTOEXTEND ON MAXSIZE 10G;
  4. 4

    Watch growth with the segment advisor

    Identify the fastest-growing segments to anticipate space needs.

    SELECT segment_name, bytes/1024/1024 mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

// common mistake

Relying on AUTOEXTEND ON with no limit seems convenient until a runaway process fills the entire disk and affects the whole instance. Always set a MAXSIZE.

// when it's worth an expert

If you run out of space repeatedly or have no growth alerts, it is worth automating the monitoring. At dba.mx we set up proactive monitoring at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.