oracle-databasetablespace

Find out free space on tablespace


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

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