How to View Tablespace in Oracle
How to View Tablespace in Oracle – Complete Guide for Oracle DBAs
Tablespaces are one of the most important storage components in an Oracle Database. As an Oracle DBA, you must regularly check tablespaces to monitor space usage, growth, performance, and availability. Understanding how to view tablespaces helps in capacity planning, avoiding space-related errors (like ORA-01653), and maintaining database health.
In this blog, we will cover all practical ways to view tablespaces in Oracle using SQL queries.
What is a Tablespace in Oracle?
A tablespace is a logical storage unit in Oracle that groups related data together. It is made up of one or more physical datafiles. Objects such as tables, indexes, and undo segments are stored inside tablespaces.
Types of tablespaces:
- SYSTEM
- SYSAUX
- UNDO
- TEMP
- USERS
- Application-specific tablespaces
1. View All Tablespaces in Database
SELECT tablespace_name, status, contents, logging
FROM dba_tablespaces;
Explanation:
TABLESPACE_NAME→ Name of the tablespaceSTATUS→ ONLINE / OFFLINE / READ ONLYCONTENTS→ PERMANENT / TEMPORARY / UNDOLOGGING→ LOGGING or NOLOGGING
2. Check Datafiles of a Tablespace
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb, status
FROM dba_data_files
ORDER BY tablespace_name;
This shows:
- Physical file location
- Size of each datafile
- Which tablespace it belongs to
3. View Free Space in Tablespaces
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024, 2) AS free_space_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY tablespace_name;
Useful to understand how much space is still available.
4. View Used Space of Tablespaces
SELECT df.tablespace_name,
ROUND((df.total_mb - fs.free_mb), 2) AS used_mb,
df.total_mb
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
This shows:
- Total size
- Used space
- Remaining space
5. View Tablespace Usage in Percentage
SELECT df.tablespace_name,
ROUND((1 - fs.free_mb/df.total_mb)*100, 2) AS used_percentage
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
Very helpful for monitoring and alert configuration.
6. View Temporary Tablespace Usage
Temporary tablespace is not shown in DBA_FREE_SPACE. Use this:
SELECT tablespace_name,
ROUND(SUM(used_blocks*8)/1024, 2) AS used_mb,
ROUND(SUM(free_blocks*8)/1024, 2) AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;
7. View Undo Tablespace
SHOW PARAMETER undo_tablespace;
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents='UNDO';
8. View Tablespace and Autoextend Status
SELECT file_name,
tablespace_name,
autoextensible,
maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files;
This helps you know whether files can grow automatically or not.
9. View Tablespace for a Specific User
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'PRADIP';
10. View Objects Stored in a Tablespace
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY size_mb DESC;
Useful for identifying which objects are consuming maximum space.
11. Quick One-Line Tablespace Report
SELECT df.tablespace_name,
df.total_mb,
NVL(fs.free_mb,0) AS free_mb,
(df.total_mb - NVL(fs.free_mb,0)) AS used_mb,
ROUND((df.total_mb - NVL(fs.free_mb,0))/df.total_mb*100,2) AS used_pct
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files
GROUP BY tablespace_name) df
LEFT JOIN
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;
This is one of the most useful queries for daily DBA monitoring.
Why Viewing Tablespaces is Important for Oracle DBAs
- Prevent ORA-01653 and space full issues
- Capacity planning and forecasting
- Performance optimization
- Database stability and availability
- Daily health checks in production
Conclusion
Viewing and monitoring tablespaces is a fundamental responsibility of every Oracle DBA. By using these SQL queries, you can easily track:
- Total size
- Used space
- Free space
- Growth behavior
- Autoextend settings
- Object-level space consumption
These queries are extremely important for real-time production environments and interview preparation as well.
At Learnomate Technologies, we believe strong DBA skills start with mastering core concepts like tablespace monitoring and storage management. This tablespace monitoring approach is part of the practical Oracle DBA training
