How to check daily growth of a specific tablespace with variance in Oracle

Given a tablespace name, print one row per day: allocated GB, current used GB, previous used GB, and a variance column labelled with the delta or ''NO DATA GROWTH'' / ''DATA PURGED''. Handy for turning AWR history into a readable diff.

DBA_HIST_TBSPC_SPACE_USAGE and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition.

ACCEPT p_tbs CHAR PROMPT 'Tablespace name: '

SELECT run_time,
       name,
       alloc_size_gb,
       curr_used_size_gb,
       prev_used_size_gb,
       variance
FROM (
  WITH t AS (
    SELECT ss.run_time,
           ts.name,
           ROUND(su.tablespace_size     * dt.block_size / 1024 / 1024 / 1024, 2) AS alloc_size_gb,
           ROUND(su.tablespace_usedsize * dt.block_size / 1024 / 1024 / 1024, 2) AS used_size_gb
    FROM   dba_hist_tbspc_space_usage su,
           (SELECT TRUNC(begin_interval_time) AS run_time,
                   MAX(snap_id)               AS snap_id
            FROM   dba_hist_snapshot
            GROUP BY TRUNC(begin_interval_time)) ss,
           v$tablespace     ts,
           dba_tablespaces  dt
    WHERE  su.snap_id       = ss.snap_id
    AND    su.tablespace_id = ts.ts#
    AND    ts.name          = UPPER('&&p_tbs')
    AND    ts.name          = dt.tablespace_name
  )
  SELECT e.run_time,
         e.name,
         e.alloc_size_gb,
         e.used_size_gb AS curr_used_size_gb,
         b.used_size_gb AS prev_used_size_gb,
         CASE WHEN e.used_size_gb > b.used_size_gb THEN TO_CHAR(e.used_size_gb - b.used_size_gb)
              WHEN e.used_size_gb = b.used_size_gb THEN '***NO DATA GROWTH'
              WHEN e.used_size_gb < b.used_size_gb THEN '**DATA PURGED'
         END AS variance
  FROM   t e, t b
  WHERE  e.run_time = b.run_time + 1
  ORDER BY 1
);