How to project Oracle tablespace growth from AWR history

Two PL/SQL variants driven from DBA_HIST_TBSPC_SPACE_USAGE (AWR). Version 1 loops every PERMANENT tablespace and prints growth history + projections per tablespace, spooling to a file. Version 2 aggregates every tablespace into a single database-wide summary with projections out to 180 days.

DBA_HIST_TBSPC_SPACE_USAGE and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition. Projection is naive linear (per-day growth * N days) — treat outputs as a first-order estimate, not a commitment.

Version 1 — per-tablespace report, spooled

SET SERVEROUT ON
SET VERIFY OFF
SET LINES 200 PAGES 2000

SPOOL tablespace_growth.txt

DECLARE
  v_ts_id                     NUMBER;
  v_ts_block_size             NUMBER;
  v_begin_snap_id             NUMBER;
  v_end_snap_id               NUMBER;
  v_begin_snap_date           DATE;
  v_end_snap_date             DATE;
  v_numdays                   NUMBER;
  v_ts_begin_size             NUMBER;
  v_ts_end_size               NUMBER;
  v_ts_growth                 NUMBER;
  v_ts_begin_allocated_space  NUMBER;
  v_ts_end_allocated_space    NUMBER;
  v_count                     NUMBER;
  not_in_awr                  EXCEPTION;

  CURSOR v_cur IS
    SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'PERMANENT';
BEGIN
  FOR v_rec IN v_cur LOOP
    BEGIN
      SELECT ts# INTO v_ts_id FROM v$tablespace WHERE name = v_rec.tablespace_name;

      SELECT COUNT(*) INTO v_count
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id;
      IF v_count = 0 THEN RAISE not_in_awr; END IF;

      SELECT block_size INTO v_ts_block_size
      FROM   dba_tablespaces
      WHERE  tablespace_name = v_rec.tablespace_name;

      SELECT MIN(snap_id), MAX(snap_id),
             MIN(TRUNC(TO_DATE(rtime, 'MM/DD/YYYY HH24:MI:SS'))),
             MAX(TRUNC(TO_DATE(rtime, 'MM/DD/YYYY HH24:MI:SS')))
      INTO   v_begin_snap_id, v_end_snap_id, v_begin_snap_date, v_end_snap_date
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id;

      v_numdays := v_end_snap_date - v_begin_snap_date;

      SELECT ROUND(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_begin_allocated_space
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id AND snap_id = v_begin_snap_id;

      SELECT ROUND(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_end_allocated_space
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id AND snap_id = v_end_snap_id;

      SELECT ROUND(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_begin_size
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id AND snap_id = v_begin_snap_id;

      SELECT ROUND(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_end_size
      FROM   dba_hist_tbspc_space_usage
      WHERE  tablespace_id = v_ts_id AND snap_id = v_end_snap_id;

      v_ts_growth := v_ts_end_size - v_ts_begin_size;

      DBMS_OUTPUT.PUT_LINE(CHR(10) || v_rec.tablespace_name || ' Tablespace');
      DBMS_OUTPUT.PUT_LINE('--------------------');
      DBMS_OUTPUT.PUT_LINE('Block size: ' || v_ts_block_size);
      DBMS_OUTPUT.PUT_LINE('Allocated: ' || v_ts_end_allocated_space || ' MB (' || ROUND(v_ts_end_allocated_space/1024,2) || ' GB)');
      DBMS_OUTPUT.PUT_LINE('Used:      ' || v_ts_end_size || ' MB (' || ROUND(v_ts_end_size/1024,2) || ' GB)');
      DBMS_OUTPUT.PUT_LINE('Used %:    ' || ROUND(v_ts_end_size/v_ts_end_allocated_space*100,2) || ' %');
      DBMS_OUTPUT.PUT_LINE('Growth in ' || v_numdays || ' days: ' || v_ts_growth || ' MB');

      IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
        DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH FOR ' || v_rec.tablespace_name || ' !!!');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Per day growth: ' || ROUND(v_ts_growth/v_numdays,2) || ' MB');
        DBMS_OUTPUT.PUT_LINE('Projected 30d:  ' || ROUND((v_ts_growth/v_numdays)*30,2)  || ' MB');
        DBMS_OUTPUT.PUT_LINE('Projected 60d:  ' || ROUND((v_ts_growth/v_numdays)*60,2)  || ' MB');
        DBMS_OUTPUT.PUT_LINE('Projected 90d:  ' || ROUND((v_ts_growth/v_numdays)*90,2)  || ' MB');
      END IF;
    EXCEPTION
      WHEN not_in_awr THEN
        DBMS_OUTPUT.PUT_LINE(CHR(10) || v_rec.tablespace_name || ': NOT FOUND IN AWR');
    END;
  END LOOP;
END;
/

SPOOL OFF

Version 2 — DB-wide aggregate with 30 to 180 day projections

SET SERVEROUT ON
SET VERIFY OFF
SET LINES 200 PAGES 2000

DECLARE
  v_ts_id                       NUMBER;
  v_ts_block_size               NUMBER;
  v_begin_snap_id               NUMBER;
  v_end_snap_id                 NUMBER;
  v_begin_snap_date             DATE;
  v_end_snap_date               DATE;
  v_numdays                     NUMBER;
  v_count                       NUMBER;
  v_ts_begin_size               NUMBER;
  v_ts_end_size                 NUMBER;
  v_ts_begin_allocated_space    NUMBER;
  v_ts_end_allocated_space      NUMBER;
  v_db_begin_size               NUMBER := 0;
  v_db_end_size                 NUMBER := 0;
  v_db_begin_allocated_space    NUMBER := 0;
  v_db_end_allocated_space      NUMBER := 0;
  v_db_growth                   NUMBER := 0;
  not_in_awr                    EXCEPTION;

  CURSOR v_cur IS
    SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'PERMANENT';
BEGIN
  FOR v_rec IN v_cur LOOP
    BEGIN
      v_ts_begin_allocated_space := 0;
      v_ts_end_allocated_space   := 0;
      v_ts_begin_size            := 0;
      v_ts_end_size              := 0;

      SELECT ts# INTO v_ts_id FROM v$tablespace WHERE name = v_rec.tablespace_name;
      SELECT block_size INTO v_ts_block_size FROM dba_tablespaces WHERE tablespace_name = v_rec.tablespace_name;

      SELECT COUNT(*) INTO v_count FROM dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id;
      IF v_count = 0 THEN RAISE not_in_awr; END IF;

      SELECT MIN(snap_id), MAX(snap_id),
             MIN(TRUNC(TO_DATE(rtime, 'MM/DD/YYYY HH24:MI:SS'))),
             MAX(TRUNC(TO_DATE(rtime, 'MM/DD/YYYY HH24:MI:SS')))
      INTO   v_begin_snap_id, v_end_snap_id, v_begin_snap_date, v_end_snap_date
      FROM   dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id;

      IF UPPER(v_rec.tablespace_name) = 'SYSTEM' THEN
        v_numdays := v_end_snap_date - v_begin_snap_date;
      END IF;

      SELECT ROUND(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_begin_allocated_space
      FROM   dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id AND snap_id = v_begin_snap_id;

      SELECT ROUND(MAX(tablespace_size) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_end_allocated_space
      FROM   dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id AND snap_id = v_end_snap_id;

      SELECT ROUND(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_begin_size
      FROM   dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id AND snap_id = v_begin_snap_id;

      SELECT ROUND(MAX(tablespace_usedsize) * v_ts_block_size / 1024 / 1024, 2)
      INTO   v_ts_end_size
      FROM   dba_hist_tbspc_space_usage WHERE tablespace_id = v_ts_id AND snap_id = v_end_snap_id;

      v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;
      v_db_end_allocated_space   := v_db_end_allocated_space   + v_ts_end_allocated_space;
      v_db_begin_size            := v_db_begin_size            + v_ts_begin_size;
      v_db_end_size              := v_db_end_size              + v_ts_end_size;
      v_db_growth                := v_db_end_size - v_db_begin_size;
    EXCEPTION
      WHEN not_in_awr THEN NULL;
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Summary');
  DBMS_OUTPUT.PUT_LINE('========');
  DBMS_OUTPUT.PUT_LINE('Allocated: ' || v_db_end_allocated_space || ' MB (' || ROUND(v_db_end_allocated_space/1024,2) || ' GB)');
  DBMS_OUTPUT.PUT_LINE('Used:      ' || v_db_end_size || ' MB (' || ROUND(v_db_end_size/1024,2) || ' GB)');
  DBMS_OUTPUT.PUT_LINE('Used %:    ' || ROUND(v_db_end_size/v_db_end_allocated_space*100,2) || ' %');
  DBMS_OUTPUT.PUT_LINE('Growth in ' || v_numdays || ' days: ' || v_db_growth || ' MB');

  IF (v_db_growth <= 0 OR v_numdays <= 0) THEN
    DBMS_OUTPUT.PUT_LINE('No data growth was found for the database');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Per day growth: '  || ROUND(v_db_growth/v_numdays,2) || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected  30d: '  || ROUND((v_db_growth/v_numdays)*30,2)  || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected  60d: '  || ROUND((v_db_growth/v_numdays)*60,2)  || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected  90d: '  || ROUND((v_db_growth/v_numdays)*90,2)  || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected 120d: '  || ROUND((v_db_growth/v_numdays)*120,2) || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected 150d: '  || ROUND((v_db_growth/v_numdays)*150,2) || ' MB');
    DBMS_OUTPUT.PUT_LINE('Projected 180d: '  || ROUND((v_db_growth/v_numdays)*180,2) || ' MB');
  END IF;
END;
/