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

From: Eugene <epipko_at_hotmail.com>
Date: 20 May 2005 10:33:58 -0700
Message-ID: <1116610437.970840.304280@f14g2000cwb.googlegroups.com>

Hi all,
You may say: There is tonn of stuff on the web and in Oracle docs, but the problem with that it that I get different results with each one(take SYSTEM ts as an example).

Example 1: Result: SYSTEM 97.71% (pct_space)

---

SELECT
    b.tablespace_name,
    b.max_size,
    NVL(a.space_used,0) space_used,
    b.max_size - NVL(a.space_used,0) space_free,     TO_CHAR(ROUND((b.max_size - NVL(a.space_used,0))/b.max_size * 100 ,2),999.99) pct_free
FROM
    (SELECT tablespace_name,

            ROUND(SUM(bytes)/1024/1024) space_used
     FROM DBA_EXTENTS
     GROUP BY tablespace_name) a,
    (SELECT df.tablespace_name,
            SUM(ROUND(NVL(fe.maxextend *
4096/1024/1024,df.bytes/1024/1024))) max_size
     FROM sys.FILEXT$ fe,
          DBA_DATA_FILES df
     WHERE fe.FILE#(+) = df.file_id
     GROUP BY df.tablespace_name) b

WHERE a.tablespace_name(+) = b.tablespace_name

Example 2: Result: SYSTEM 26.63% (pct_space)

---

select Tablespace_Name,

       Max_Blocks,
       Count_Blocks,
       Sum_Free_Blocks,
       round(100*Sum_Free_Blocks/Sum_Alloc_Blocks,2) AS Pct_Free
  from (select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks
          from DBA_DATA_FILES
         group by Tablespace_Name),
       (select Tablespace_Name FS_TS_NAME,
               MAX(Blocks) AS Max_Blocks,
               COUNT(Blocks) AS Count_Blocks,
               SUM(Blocks) AS Sum_Free_Blocks
          from DBA_FREE_SPACE
         group by Tablespace_Name)

 where Tablespace_Name = FS_TS_NAME

Example 3: Result: SYSTEM 2.05% (pct_space)

---

SELECT df.tablespace_name, SUM(df.bytes) TOTAL_SPACE, SUM(fs.bytes) FREE_SPACE,
ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) PCT_FREE FROM dba_free_space fs, dba_data_files df WHERE df.tablespace_name = fs.tablespace_name (+)   and ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) < 30 GROUP BY df.tablespace_name
ORDER BY df.tablespace_name;

I'm really confised as what script to use and why are they all produce different results?

Thanks,
Eugene Received on Fri May 20 2005 - 12:33:58 CDT