Re: so, what is the right way to count tablespace free space

From: Eugene <epipko_at_hotmail.com>
Date: 20 May 2005 13:30:23 -0700
Message-ID: <1116621023.475980.231850@g14g2000cwa.googlegroups.com>

Thanks for replies,
I went to see if Tom Kyte had anything similar and ... his version of that was:

---

select (select decode(extent_management,'LOCAL','*',' ') || decode(segment_space_management,'AUTO','a ','m ')

         from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,

         nvl(a.tablespace_name,'UNKOWN')) name,
         kbytes_alloc kbytes,
         kbytes_alloc-nvl(kbytes_free,0) used,
         nvl(kbytes_free,0) free,
         round(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100)
pct_used,
         round((100 -


((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100),3) pct_free,

         nvl(largest,0) largest,
         nvl(kbytes_max,kbytes_alloc) Max_Size,
         round(decode( kbytes_max, 0, 0,

(kbytes_alloc/kbytes_max)*100),3) pct_max_used
  from ( select sum(bytes)/1024 Kbytes_free,

                max(bytes)/1024 largest,
                tablespace_name
         from  sys.dba_free_space
         group by tablespace_name ) a,
       ( select sum(bytes)/1024 Kbytes_alloc,
                sum(maxbytes)/1024 Kbytes_max,
                tablespace_name
             from sys.dba_data_files
             group by tablespace_name
             union all
        select sum(bytes)/1024 Kbytes_alloc,
               sum(maxbytes)/1024 Kbytes_max,
               tablespace_name
        from sys.dba_temp_files
        group by tablespace_name )b

  where a.tablespace_name (+) = b.tablespace_name

---

Eugene Received on Fri May 20 2005 - 15:30:23 CDT