🧩 How to Check Tablespace Growth in Oracle Database

🧩 Introduction

Monitoring tablespace growth is important for capacity planning and preventing database outages due to insufficient space. As a DBA, you need to track how tablespaces are growing over time and estimate when you might need to add more space.

Step 1: View Current Tablespace Sizes

Use this query to check the current size of each tablespace:

SELECT tablespace_name,

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

FROM dba_data_files

GROUP BY tablespace_name

ORDER BY size_mb DESC;

➡️ This shows the allocated size of each tablespace.

Step 2: Check Free Space in Tablespaces

To know how much space is available within each tablespace:

SELECT tablespace_name,

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

FROM dba_free_space

GROUP BY tablespace_name

ORDER BY free_mb DESC;

➡️ Compare with the allocated size to determine usage percentage.

Step 3: Monitor Growth Using AWR or Statspack

Oracle AWR (Automatic Workload Repository) or Statspack snapshots provide historical data on tablespace usage. You can generate reports to analyze growth trends.

Example AWR report query:

SELECT * FROM dba_hist_tbspc_space_usage

WHERE tablespace_id = (SELECT ts# FROM v$tablespace WHERE name='USERS');

➡️ Replace 'USERS' with the name of the tablespace you want to track.

Step 4: Estimate Future Growth

To estimate when space will run out, track usage over time (daily/weekly) and calculate the growth rate. A simple approach is to save query results into a monitoring table or use enterprise tools like Oracle Enterprise Manager (OEM).

Step 5: Add Space if Needed

When growth is high, increase the tablespace size:

ALTER DATABASE DATAFILE '/u01/oradata/ORA/users01.dbf' RESIZE 1G;

Or add a new datafile:

ALTER DATABASE ADD DATAFILE '/u01/oradata/ORA/users02.dbf' SIZE 500M AUTOEXTEND ON;

➡️ Autoextend helps avoid sudden space issues but must be monitored carefully.

Conclusion

Tracking tablespace growth is a proactive DBA practice that prevents critical errors like ORA-01653: unable to extend table. By monitoring regularly and planning for future growth, you ensure stable and reliable database performance.