Saturday, February 24, 2018

Check tablespace usage

One of the most problem when monitoring team or dba wants to check tablespace usage is real space.
In fact in the most query the query shows free space base on amount of extended space and somebody has problem because you have to calculate that how much space is remaining on your tablespace of whole. I changed a script for getting to this purpose and also it supports bigfile. you can  use it below query o check your real free space of your tablespaces.

--if bigfile=no the each datafile=32 GB
--if bigfile=YES the each datafile=32768 GB
--and also  db_block_size=8192


SELECT *
  FROM (SELECT SUBSTR(c.contents, 1, 1) C,
               a.tablespace_name,
               a.alloc_gb,
               a.alloc_gb - nvl(b.free_gb, 0) used_gb,
               nvl(b.free_gb, 0) free_gb,
               
               a.file_count,
               CASE
                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.alloc_gb * 100)
                 ELSE
                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.max_gb * 100)
               END pct_used,
               
               case
                 when (c.bigfile = 'NO') then
                  (32 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
                 when (c.bigfile = 'YES') then
                  (32768 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
               end real_free_SIZE_GB
        
          FROM (SELECT tablespace_name,
                       COUNT(*) file_count,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
                  FROM dba_data_files
                 GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) free_gb
                  FROM dba_free_space
                 GROUP BY tablespace_name) b,
               dba_tablespaces c
         WHERE a.tablespace_name = b.tablespace_name(+)
           AND a.tablespace_name = c.tablespace_name
        
        UNION
        
        SELECT SUBSTR(c.contents, 1, 1) C,
               a.tablespace_name,
               a.alloc_gb,
               NVL(b.used_gb, 0) used_gb,
               a.alloc_gb - NVL(b.used_gb, 0) free_gb,
               
               a.file_count,
               CASE
                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
                  ROUND(NVL(b.used_gb, 0) / a.alloc_gb * 100)
                 ELSE
                  ROUND(NVL(b.used_gb, 0) / a.max_gb * 100)
               END
               
               pct_used,
               
               case
                 when (c.bigfile = 'NO') then
                  (32 * a.file_count) - (used_gb)
                 when (c.bigfile = 'YES') then
                  (32768 * a.file_count) - (used_gb)
               end real_free_SIZE_GB
        
          FROM (SELECT tablespace_name,
                       COUNT(*) file_count,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
                  FROM dba_temp_files
                 GROUP BY tablespace_name) a,
               (SELECT tablespace tablespace_name,
                       ROUND(SUM(blocks * b.VALUE) / 1024 / 1024 / 1024, 2) used_gb
                  FROM v$tempseg_usage a, v$parameter b
                 WHERE b.name = 'db_block_size'
                 GROUP BY tablespace) b,
               dba_tablespaces c
         WHERE a.tablespace_name = b.tablespace_name(+)
           AND a.tablespace_name = c.tablespace_name)
 ORDER BY 7 DESC NULLS LAST, 2

No comments:

Post a Comment