How to Check and Manage Oracle Tablespace Usage

Introduction

Monitoring tablespace is crucial to avoid database errors due to full space.

Steps:

1. Check Tablespace Usage

SELECT tablespace_name, 

       ROUND((used_space/1024/1024),2) AS used_mb, 

       ROUND((free_space/1024/1024),2) AS free_mb

FROM dba_tablespace_usage_metrics;

2. Add Datafile if Needed

ALTER DATABASE DATAFILE '/u01/PROD/oradata/DB/users01.dbf' RESIZE 500M;

-- Or

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

3. Monitor Regularly

Schedule a daily check with a cron job.

Conclusion

Keeping tablespace under control prevents downtime and helps maintain database stability.