Query - Tablespace Max Size

I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did...

For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files...

Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.


  SELECT tablespace_name,
         ROUND (SUM (bytes) / 1048576) curr_size,
         ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0)))/ 1048576)  growable
    FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;