Check Oracle Tablespace Free Space
Disclaimer
Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all
Introduction
This article shows how to view space utilization statistics for the Oracle tablespaces on your system. Using a command line script you can see how much space has been allocated, how much has been used, and how much is free.
You might run this because you‘re concerned that you‘re nearly out of free space, or on the other hand, you might suspect that a tablespace has more free space than it really needs.
Solution
You will need to run the script below under the System account. The screenshot shows how to open a SQL Worksheet in SQL Developer by right clicking the System connection, assuming you have already created a Connection under the System username. The script could also be run from the command line in SQL *Plus after logging in with the System userid.
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Here's what the output looked like on my system:
Tablespace Used MB Free MB Total MB Pct. Free ------------------------------ ---------- ---------- ---------- ---------- SYSAUX 735 45 780 6 EAS 0 2048 2048 100 PLANSYS 1 2047 2048 100 BIPLUS 83 1965 2048 96 UNDOTBS1 29 696 725 96 USERS 1 4 5 80 SYSTEM 792 8 800 1 EXAMPLE 34 289 323 89 PLANAPP1 25 2023 2048 99 PLANSAMP 27 2021 2048 99 EPMA 41 2007 2048 98 Tablespace Used MB Free MB Total MB Pct. Free ------------------------------ ---------- ---------- ---------- ---------- SS 25 2023 2048 99 CALC 12 2036 2048 99 VISION 27 2021 2048 99 14 rows selected
Final Remarks
It looks like some of the tablespaces are larger than necessary. For example, the VISION tablespace is allocated for 2GB but is only using 27MB. If you find that you have a datafile that's bigger than necessary, here's an article that shows how to reclaim some of that space: Reduce Datafile Size
If you have any questions about this article or would like to discuss a consulting engagement, please email me at my G-mail account, which has a userid of "tcockerline".
-Tom Cockerline, February 2017