Saturday, November 12, 2016

Oracle - List all my tablespaces free space (autoextend on)

WITH tmp AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   tmp.tablespace_name,
         ROUND (SUM (tmp.mysize) / (1024 * 1024)) total_size,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) datafile_free_space,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) total_free_space,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (tmp.mysize)
                 * 100
               ) percent_free
    FROM tmp, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE tmp.file_id = dfs.file_id(+)
GROUP BY tmp.tablespace_name
ORDER BY 6 DESC

No comments:

Post a Comment