🧩 How to Check Tablespace Usage in Oracle Database

🧩 Introduction

Monitoring tablespace usage is an essential task for Oracle DBAs. If a tablespace becomes full, database operations may fail. By regularly checking usage, you can take preventive actions like resizing datafiles or adding new ones.

Step 1: Check Tablespace Usage with DBA Views

SELECT tablespace_name,

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

       ROUND(tablespace_size*8/1024,2) AS total_mb,

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

FROM dba_tablespace_usage_metrics;

➡️ This query shows tablespace name, used size (MB), total size (MB), and percentage used.

Step 2: Check Free Space in Each Tablespace

SELECT tablespace_name,

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

FROM dba_free_space

GROUP BY tablespace_name;

➡️ This helps identify how much free space is left in each tablespace.

Step 3: Check Datafiles in a Tablespace

SELECT file_name, tablespace_name,

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

       autoextensible

FROM dba_data_files;

➡️ This shows file details and whether they are AUTOEXTENSIBLE.

Conclusion

By monitoring tablespace usage, DBAs can avoid “tablespace full” errors and ensure smooth database operations. Regular checks and proactive resizing keep the system stable.