Find out free space on tablespace

Question

Our application has failed a few times because an 'ORA-01536: space quota exceeded for tablespace', and we would like to be able to prevent this by checking regularly the free space on the tablespace and raising an alert when it drops below certain level.

Is there any way to find out how much free space is left in a tablespace?

After some research (I am not a DBA), I tried the following:

select max_bytes-bytes from user_ts_quotas;

select sum(nvl(bytes,0)) from user_free_space;

but those queries return completely different results.

Solution 1

I use this query

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

Solution 2

A much more accurate SQL STATEMENT

SELECT  a.tablespace_name,
    ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
    c.BYTES / 1024 / 1024 space_allocated,
    ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
    ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free, 
    c.DATAFILES
  FROM dba_tablespaces a,
       (    SELECT   tablespace_name, 
                  SUM (BYTES) BYTES
           FROM   dba_free_space
       GROUP BY   tablespace_name
       ) b,
      (    SELECT   COUNT (1) DATAFILES, 
                  SUM (BYTES) BYTES, 
                  tablespace_name
           FROM   dba_data_files
       GROUP BY   tablespace_name
    ) c
  WHERE b.tablespace_name(+) = a.tablespace_name 
    AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

Solution 3

There are many ways to check the size, but as a developer we dont have much access to query meta tables, I find this solution very easy (Note: if you are getting error message ORA-01653 ‘The ORA-01653 error is caused because you need to add space to a tablespace.’)

--Size of All Table Space

--1. Used Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME
--2. Free Space
SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME

--3. Both Free & Used
SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
INNER JOIN
(SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

Thanks

Solution 4

This is one of the simplest query for the same that I came across and we use it for monitoring as well:

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

A complete article about Oracle Tablespace: Tablespace

Solution 5

this is pretty good as well

clear breaks
clear computes

Prompt
Prompt Tablespace Usage
Prompt

SET lines 120 pages 500 
col percent_used format 999.99

SELECT a.TABLESPACE_NAME,
  NVL(ROUND((a.BYTES  /1024)/1024/1024,2),2) GB_TOTAL,
  NVL(ROUND((b.BYTES  /1024)/1024/1024,2),2) GB_FREE,
  NVL(ROUND((b.largest/1024),2),0) KB_Chunk,
  NVL(ROUND(((a.BYTES -NVL(b.BYTES,1))/a.BYTES)*100,4),0) percent_used
FROM
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),0) BYTES
  FROM dba_data_files
  GROUP BY TABLESPACE_NAME
  ) a,
  (SELECT TABLESPACE_NAME,
    NVL(SUM(BYTES),1) BYTES ,
    NVL(MAX(BYTES),1) largest
  FROM dba_free_space
  GROUP BY TABLESPACE_NAME
  ) b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;

output

TABLESPACE_NAME                  GB_TOTAL    GB_FREE   KB_CHUNK PERCENT_USED
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                                .84        .02       9216        97.36
SYSAUX                                .57        .05      32768        91.10
UNDOTBS1                              .06        .05      36864        23.13
USERS                                   0          0       4096        20.00

View more solutions

Comments

[Denys]

this one has a TEMP tablespace, which wasn't displayed when using the query from the answer

[Zardoz89]

I personally check that this is pretty fast. 6 seconds vs 188 seconds that have a similar query that we have being used from some time ago., on a Oracle 12 database.

[Ryan S]

Nice - this gives the same number as Enterprise manager; awesome! I trust this now =)

[Raja Anbazhagan]

I'm sorry, But what is the unit of size we are speaking here..?

[AngelWarrior]

@RajaAnbazhagan - units here are MB = bytes / 1024 / 1024

[rene]

Why is this sql statement better then the others?

[Joe C]

Welcome to Stack Overflow! Code-only answers are not very helpful. Please edit your answer to explain why your code solves the original problem.

[xtsoler]

I tried it and it seems that the list will not include a completely full tablespace because there is no entry for it in the dba_free_space. (I'm on 10g)

[Momergil]

I tried your solution in my database and it failed.

[marsh-wiggle]

How does this differ from the already given answer?: stackoverflow.com/a/15066653/1574221

[AdityaKeyal]

Welcome to StackOverflow. As mentioned by @marsh-wiggle, please provide explanation why your answer is better/different from the already provided answers.