How to project tablespace growth per PDB in Oracle Multitenant

Multitenant version: iterate V$CONTAINERS (skip PDB$SEED), then loop every PERMANENT tablespace inside each PDB, and print a growth history and projection for each. Uses CDB_TABLESPACES and DBA_HIST_TBSPC_SPACE_USAGE with con_id filters.

Requires Oracle Multitenant (12c and later). DBA_HIST_TBSPC_SPACE_USAGE and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition. Run from the CDB root as a common DBA user.

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_ts_begin_size            NUMBER;
  v_ts_end_size              NUMBER;
  v_ts_growth                NUMBER;
  v_count                    NUMBER;
  v_ts_begin_allocated_space NUMBER;
  v_ts_end_allocated_space   NUMBER;
  not_in_awr                 EXCEPTION;

  CURSOR v_con_cur IS
    SELECT con_id, name FROM v$containers WHERE name <> 'PDB$SEED';

  CURSOR v_ts_cur (par_con_id NUMBER) IS
    SELECT tablespace_name
    FROM   cdb_tablespaces
    WHERE  contents = 'PERMANENT'
    AND    con_id   = par_con_id;
BEGIN
  FOR v_con_rec IN v_con_cur LOOP
    DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Container = ' || v_con_rec.name);
    DBMS_OUTPUT.PUT_LINE('====================');

    FOR v_ts_rec IN v_ts_cur(v_con_rec.con_id) LOOP
      BEGIN
        SELECT ts# INTO v_ts_id
        FROM   v$tablespace
        WHERE  name   = v_ts_rec.tablespace_name
        AND    con_id = v_con_rec.con_id;

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

        SELECT block_size INTO v_ts_block_size
        FROM   cdb_tablespaces
        WHERE  tablespace_name = v_ts_rec.tablespace_name
        AND    con_id          = v_con_rec.con_id;

        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
        AND    con_id        = v_con_rec.con_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 con_id = v_con_rec.con_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 con_id = v_con_rec.con_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 con_id = v_con_rec.con_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 con_id = v_con_rec.con_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_ts_rec.tablespace_name);
        DBMS_OUTPUT.PUT_LINE('  Block size: ' || v_ts_block_size);
        DBMS_OUTPUT.PUT_LINE('  Allocated: '  || v_ts_end_allocated_space || ' MB');
        DBMS_OUTPUT.PUT_LINE('  Used:      '  || v_ts_end_size || ' MB');
        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 GROWTH FOR ' || v_ts_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_ts_rec.tablespace_name || ': NOT FOUND IN AWR');
      END;
    END LOOP;
  END LOOP;
END;
/