🧩 How to Check Tablespace Usage in Oracle Database

🧩 Introduction

Monitoring tablespace usage is a critical task for DBAs. If a tablespace becomes full, it can cause application errors and prevent new data from being inserted.

Step 1: Query Tablespace Usage

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 shows total space, used space, and percentage used for each tablespace.

Step 2: Identify Datafiles of a Tablespace

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

FROM dba_data_files

WHERE tablespace_name='USERS';

➡️ Replace USERS with your tablespace name.

Step 3: Add Space to a Tablespace

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

➡️ You can either resize the datafile or add a new one.

Conclusion

By monitoring tablespace usage regularly, DBAs can prevent space issues and ensure smooth database operations.