How to Monitor Free and Used Space in Oracle Database
- Vinay Shree Shukla
- Mar 24
- 3 min read
Updated: 6 days ago
As a Database Administrator (DBA), keeping track of storage usage is essential for maintaining smooth database performance. Insufficient storage can lead to issues such as tablespace overflows, transaction failures, and performance degradation.
This guide provides step-by-step SQL queries to check free space, used space, and storage allocation in an Oracle database.
Why is Monitoring Storage Space Important?
Oracle databases store data within tablespaces, which are made up of data files. Over time, as data increases, these tablespaces may reach their limits. Regular monitoring helps in:
Preventing Storage Issues – Avoid running out of space in tablespaces.
Optimizing Performance – Ensuring database operations run efficiently.
Planning for Future Growth – Allocating storage resources effectively.
Step 1: Check Total Database Size (Free & Used Space)
To get an overview of the total database storage:
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_size_gb
FROM dba_data_files;
This returns the total size of all tablespaces combined in gigabytes (GB).
To include TEMP tablespace, run:
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_size_gb FROM dba_data_files
UNION ALL
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_size_gb FROM dba_temp_files;
This query provides the total database size including temporary tablespaces.
Step 2: Check Free and Used Space in Tablespaces
To find out how much free space is available in each tablespace:
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS free_space_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_space_mb DESC;
🔹 This lists free space (in MB) per tablespace, sorted from highest to lowest.
Check Used Space in Tablespaces
To see used and free space in each tablespace along with usage percentage:
SELECT a.tablespace_name,
ROUND(NVL(b.free_space, 0), 2) AS free_space_mb,
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_size FROM dba_data_files GROUP BY tablespace_name) a
LEFT JOIN
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_space FROM dba_free_space GROUP BY tablespace_name) b
ON a.tablespace_name = b.tablespace_name
ORDER BY used_percent DESC;
🔹 If USED_PERCENT exceeds 90%, consider adding more space to the tablespace.
Step 3: Check Datafile Sizes and Free Space
To check the size of each datafile and whether it can expand automatically:
SELECT file_name, tablespace_name,
ROUND(bytes/1024/1024, 2) AS file_size_mb,
autoextensible
FROM dba_data_files
ORDER BY file_size_mb DESC;
AUTOEXTENSIBLE = YES means the file can increase in size automatically.
If a file is not autoextensible, you may need to manually allocate more space.
Step 4: Monitor TEMP Tablespace Usage
The TEMP tablespace is used for temporary operations like sorting and joins.
Check TEMP Tablespace Size
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS temp_size_mb
FROM dba_temp_files
GROUP BY tablespace_name;
Check TEMP Tablespace Usage
To find out how much temporary space is currently being used:
SELECT tablespace_name,
ROUND(SUM(bytes_used)/1024/1024, 2) AS used_temp_mb
FROM V$TEMP_SPACE_HEADER
GROUP BY tablespace_name;
If TEMP usage is consistently high, consider increasing TEMP tablespace size.
Step 5: Check Free Space in UNDO Tablespace
The UNDO tablespace stores undo records for long-running transactions. To check its free space:
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS free_undo_mb
FROM dba_free_space
WHERE tablespace_name = (SELECT tablespace_name FROM dba_rollback_segs WHERE status = 'ONLINE')
GROUP BY tablespace_name;
🔹 If UNDO space is low, large transactions may fail.
Step 6: Find Large Objects Using the Most Space
To identify the largest objects (tables, indexes, LOBs) consuming storage:
SELECT segment_name, segment_type, tablespace_name,
ROUND(bytes/1024/1024, 2) AS size_mb
FROM dba_segments
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;
This query helps identify objects that are using excessive space.
By running these SQL queries, you can effectively track and manage storage space in an Oracle database. Regular monitoring prevents storage-related issues, ensures optimal database performance, and helps plan for future expansion.
Key Takeaways:
Use dba_data_files to check total database size.
Use dba_free_space to monitor available tablespace storage.
Track tablespace usage and datafile autoextensibility.
Monitor TEMP and UNDO tablespaces for performance tuning.
dentify large database objects consuming excessive space.
Maintaining proactive space management is crucial for keeping databases running efficiently and avoiding unexpected downtime!
Comments