🧩 How to Check Tablespace Usage in Oracle

🧩 Introduction

Monitoring tablespace usage is one of the most important tasks for a DBA. If a tablespace becomes full, users may face errors when inserting or updating data. In this post, we will learn how to check tablespace usage in Oracle.

🔹 Step 1: View Tablespace Usage Summary

SELECT tablespace_name,

       ROUND((used_space / tablespace_size) * 100, 2) AS used_percent

FROM dba_tablespace_usage_metrics;

➡️ This shows each tablespace with its usage percentage.

🔹 Step 2: Find Free and Used Space (in MB)

SELECT tablespace_name,

       file_id,

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

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

FROM dba_data_files;

➡️ This gives you the size of each datafile and how much space is still free.

🔹 Step 3: Check Autoextend Status

SELECT file_name, autoextensible, bytes/1024/1024 AS size_mb

FROM dba_data_files;

➡️ This tells you if datafiles can automatically grow when space runs out.

✅ Conclusion

By monitoring tablespace usage regularly, DBAs can avoid space-related issues and ensure smooth database operations. Always keep an eye on growth and enable autoextend if needed.