DBA Hub

📋Steps in this guide1/13

How to View Tablespace in Oracle

Learn how to view tablespace in Oracle using practical SQL queries. Check tablespace size, free space, used space, autoextend, TEMP and UNDO usage with this complete Oracle DBA guide by Learnomate Technologies.

oracle configurationintermediate
by OracleDba
17 views
1

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
2

1. View All Tablespaces in Database

Explanation: - → Name of the tablespace - → ONLINE / OFFLINE / READ ONLY - → PERMANENT / TEMPORARY / UNDO - → LOGGING or NOLOGGING

Code/Command (click line numbers to comment):

1
2
SELECT tablespace_name, status, contents, logging
FROM dba_tablespaces;
3

2. Check Datafiles of a Tablespace

This shows: - Physical file location - Size of each datafile - Which tablespace it belongs to

Code/Command (click line numbers to comment):

1
2
3
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb, status
FROM dba_data_files
ORDER BY tablespace_name;
4

3. View Free Space in Tablespaces

Useful to understand how much space is still available.

Code/Command (click line numbers to comment):

1
2
3
4
5
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;
5

4. View Used Space of Tablespaces

This shows: - Total size - Used space - Remaining space

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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;
6

5. View Tablespace Usage in Percentage

Very helpful for monitoring and alert configuration.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;
7

6. View Temporary Tablespace Usage

Temporary tablespace is not shown in . Use this:

Code/Command (click line numbers to comment):

1
2
3
4
5
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;
8

7. View Undo Tablespace

Code/Command (click line numbers to comment):

1
2
3
4
5
SHOW PARAMETER undo_tablespace;

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents='UNDO';
9

8. View Tablespace and Autoextend Status

This helps you know whether files can grow automatically or not.

Code/Command (click line numbers to comment):

1
2
3
4
5
SELECT file_name,
       tablespace_name,
       autoextensible,
       maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files;
10

9. View Tablespace for a Specific User

Code/Command (click line numbers to comment):

1
2
3
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'PRADIP';
11

10. View Objects Stored in a Tablespace

Useful for identifying which objects are consuming maximum space.

Code/Command (click line numbers to comment):

1
2
3
4
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY size_mb DESC;
12

11. Quick One-Line Tablespace Report

This is one of the most useful queries for daily DBA monitoring.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;
13

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!