Oracle

Oracle Tablespace Growth Analytics: Track and Forecast Database Space Usage

August 13, 2025 • By  • 10 Min Read

Monitoring Oracle tablespace growth can be a significant challenge for DBAs, While exploring solutions to track storage consumption across production databases, I discovered that Oracle’s Automatic Workload Repository (AWR) contains valuable historical data that can reveal growth patterns. I’ve refined an existing script (credit to the references below) to transform this raw data into insightful, Markdown-formatted reports that visualize both historical usage and projected growth trends.

The formatted output integrates seamlessly with documentation systems, knowledge bases, and even presentation slides, making it invaluable for capacity planning discussions. By extracting and presenting this data effectively, what was once a monitoring challenge becomes a proactive planning opportunity.

In order to produce reports, access AWR views or use the diagnostic information from any part of the Automatic Workload Repository the Diagnostic Pack License is required.

Why Monitor Tablespace Growth?

Effective database administration requires proactive space management. By tracking tablespace utilization over time, you can:

  • Identify abnormal growth patterns before they cause outages
  • Plan capacity additions based on historical trends
  • Optimize storage allocation across tablespaces
  • Provide accurate forecasting to management and procurement teams
  • Optimize storage allocation across tablespaces
  • Provide accurate forecasting to management and procurement teams

The Tablespace Growth Analyzer Script

This PL/SQL script leverages Oracle’s Automatic Workload Repository (AWR) to extract historical space usage data for all permanent tablespaces. It produces a markdown-formatted report showing:

  • Current tablespace allocation and usage statistics
  • Historical growth over the available AWR retention period
  • Projected growth for 30, 60, and 90-day periods
set serverout on
set verify off
set lines 200
set pages 2000

DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
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;
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('');
      DBMS_OUTPUT.PUT_LINE('## '||v_rec.tablespace_name||' Tablespace');
      DBMS_OUTPUT.PUT_LINE('');
      DBMS_OUTPUT.PUT_LINE('| Property                | Value                        |');
      DBMS_OUTPUT.PUT_LINE('|-------------------------|-----------------------------|');
      DBMS_OUTPUT.PUT_LINE('| Block Size              | '||v_ts_block_size||'                        |');
      DBMS_OUTPUT.PUT_LINE('| Allocated Space         | '||v_ts_end_allocated_space||' MB ('||round(v_ts_end_allocated_space/1024,2)||' GB) |');
      DBMS_OUTPUT.PUT_LINE('| Used Space              | '||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('');
      DBMS_OUTPUT.PUT_LINE('### History');
      DBMS_OUTPUT.PUT_LINE('');
      DBMS_OUTPUT.PUT_LINE('| Date         | Allocated Space      | Used Space           |');
      DBMS_OUTPUT.PUT_LINE('|--------------|---------------------|----------------------|');
      DBMS_OUTPUT.PUT_LINE('| '||v_begin_snap_date||' | '||v_ts_begin_allocated_space||' MB ('||round(v_ts_begin_allocated_space/1024,2)||' GB) | '||v_ts_begin_size||' MB ('||round(v_ts_begin_size/1024,2)||' GB) |');
      DBMS_OUTPUT.PUT_LINE('| '||v_end_snap_date||' | '||v_ts_end_allocated_space||' MB ('||round(v_ts_end_allocated_space/1024,2)||' GB) | '||v_ts_end_size||' MB ('||round(v_ts_end_size/1024,2)||' GB) |');
      DBMS_OUTPUT.PUT_LINE('');
      DBMS_OUTPUT.PUT_LINE('- **Total growth ('||v_numdays||' days):** '||v_ts_growth||' MB ('||round(v_ts_growth/1024,2)||' GB)');
      IF (v_ts_growth > 0 AND v_numdays > 0) THEN
        DBMS_OUTPUT.PUT_LINE('- **Per day growth:** '||round(v_ts_growth/v_numdays,2)||' MB ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('### Expected Growth');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('| Period         | Expected Growth      |');
        DBMS_OUTPUT.PUT_LINE('|---------------|---------------------|');
        DBMS_OUTPUT.PUT_LINE('| Next 30 days  | '||round((v_ts_growth/v_numdays)*30,2)||' MB ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB) |');
        DBMS_OUTPUT.PUT_LINE('| Next 60 days  | '||round((v_ts_growth/v_numdays)*60,2)||' MB ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB) |');
        DBMS_OUTPUT.PUT_LINE('| Next 90 days  | '||round((v_ts_growth/v_numdays)*90,2)||' MB ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB) |');
      ELSE
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('> **No data growth was found for tablespace '||v_rec.tablespace_name||'**');
      END IF;
      DBMS_OUTPUT.PUT_LINE('---');
    EXCEPTION
      WHEN not_in_awr THEN
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('## '||v_rec.tablespace_name||' Tablespace');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('> **Tablespace usage information not found in AWR!**');
        DBMS_OUTPUT.PUT_LINE('---');
    END;
  END LOOP;
END;
/

How to Use the Script

  1. Connect to your database using SQL*Plus or any SQL client with database administration privileges:

    sqlplus / as sysdba

  2. Execute the script directly or save it to a file (e.g., tablespace_growth.sql) and run:

    @tablespace_growth.sql

  3. View the results in the output pane. The script generates markdown-formatted tables and lists for each tablespace.

  4. Save the output for documentation or sharing:

    • Redirect to a file: spool tablespace_growth_report.md
    • Copy/paste into your documentation system (BookStack, Confluence, etc.)
    • Import into VS Code or other markdown editor for further formatting

  5. Generate formatted reports by copying the output into your preferred markdown editor or documentation system.

If you work with BookStack, Confluence, or any markdown-compatible system, you can easily integrate this output into your knowledge base or documentation.

BookStack example:

  • Create a new page in your documentation system.

  • Edit the draft to include the tablespace growth report.

  • Switch to the Markdown Editor if necessary.

    Switch

  • Paste the copied output into the editor.

    Paste code

  • Format the content as needed using the editor’s tools.

  • Save the page and export or share it as required.

Other Markdown Editors Online

  • Dillinger : A cloud-enabled, mobile-ready, offline-storage-compatible Markdown editor that you can use to format your report.

    • For example, you can paste the output and export it as HTML or PDF for sharing.
      Export

  • StackEdit : An in-browser Markdown editor that offers synchronization with Google Drive and Dropbox.

  • Markdown Live Preview: A simple online editor that shows a live preview of your Markdown content.

Understanding the Output

For each tablespace, the script produces a report with three main sections:

1. Current Status

Shows the current allocation, usage, and free space percentage:

## USERS Tablespace

| Property                | Value                        |
|-------------------------|-----------------------------|
| Block Size              | 8192                        |
| Allocated Space         | 512.00 MB (0.50 GB) |
| Used Space              | 328.45 MB (0.32 GB) |
| % Used                  | 64.15% |

2. Historical Usage

Displays the usage at the beginning and end of the analysis period:

### History

| Date         | Allocated Space      | Used Space           |
|--------------|---------------------|----------------------|
| 10-MAY-2025  | 256.00 MB (0.25 GB) | 209.32 MB (0.20 GB) |
| 10-AUG-2025  | 512.00 MB (0.50 GB) | 328.45 MB (0.32 GB) |

- **Total growth (92 days):** 119.13 MB (0.12 GB)
- **Per day growth:** 1.29 MB (0.00 GB)

3. Growth Projections

Forecasts future space needs based on historical growth rates:

### Expected Growth

| Period         | Expected Growth      |
|---------------|---------------------|
| Next 30 days  | 38.70 MB (0.04 GB) |
| Next 60 days  | 77.40 MB (0.08 GB) |
| Next 90 days  | 116.10 MB (0.11 GB) |

Use Cases for This Report

This script is particularly valuable for:

  • Quarterly storage planning: Generate reports at the beginning of each quarter to forecast upcoming storage needs
  • Troubleshooting sudden space issues: Identify which tablespaces are growing faster than expected
  • Migration planning: When moving to new hardware or cloud services, accurately estimate required storage
  • Performance tuning: Correlate growth patterns with application changes or usage patterns

Limitations and Considerations

  • AWR Retention: The script can only analyze data retained in the AWR.
  • Linear Projections: Growth forecasts assume linear growth based on historical averages. Seasonal variations or application changes may affect actual growth.
  • Privileges Required: You need SYSDBA or specific monitoring privileges to access the required views.

Final Thoughts

Oracle’s AWR contains valuable information about tablespace growth patterns that can help you avoid storage-related outages and plan capacity more effectively. This script transforms that raw data into actionable insights you can use for proactive database management.

For optimal results, I recommend running this script monthly and storing the results for trend analysis over longer periods. With this information at your fingertips, you’ll never be caught by surprise when a tablespace needs expansion.

Note: This script has been tested on Oracle Database 12C, 19c and 23c. Earlier versions may require adjustments to the AWR view queries.

References and Additional Resources

This script was developed based on Oracle’s documentation and community best practices. For further reading on tablespace management and AWR analysis, please refer to these resources:

  1. Github designworld-ca: Github designworld-ca

  2. Oracle Support Document 1514861.1: How to Change the Period of Time Data is Kept in DBA_HIST_TBSPC_SPACE_USAGE ?

  3. Oracle Support Document 1490798.1: “AWR Reporting – Licensing Requirements Clarification”

Leave a Reply

Your email address will not be published. Required fields are marked *