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.

Answer 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; 

Answer 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;