How to Check Database Growth in Oracle

 Introduction

Monitoring database growth helps DBAs plan for storage, avoid space issues, and maintain performance. Oracle provides views and queries to track how much the database is growing over time.

Step 1: Check Tablespace Sizes

SELECT tablespace_name,

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb

FROM dba_data_files

GROUP BY tablespace_name;

This shows allocated space per tablespace.

Step 2: Check Free Space

SELECT tablespace_name,

       ROUND(SUM(bytes) / 1024 / 1024, 2) AS free_mb

FROM dba_free_space

GROUP BY tablespace_name;

Compare with Step 1 to see usage.

Step 3: Historical Growth Using AWR (if licensed)

SELECT snap_id, begin_interval_time, end_interval_time,

       tablespace_name,

       (tablespace_size * block_size)/1024/1024 AS size_mb,

       (tablespace_usedsize * block_size)/1024/1024 AS used_mb

FROM dba_hist_tbspc_space_usage

JOIN dba_hist_snapshot USING (snap_id, dbid, instance_number);

This shows growth over time (requires Diagnostic Pack license).

Step 4: Simple Trend with DBA_SEGMENTS

SELECT owner, segment_type,

       ROUND(SUM(bytes)/1024/1024,2) AS size_mb

FROM dba_segments

GROUP BY owner, segment_type

ORDER BY size_mb DESC;

This shows which objects are consuming the most space.

Conclusion

By regularly checking database growth, you can plan storage, prevent out-of-space errors, and maintain system stability. Always combine monitoring with proactive housekeeping tasks (purging old data, archiving logs, and resizing tablespaces).