6. A Sample Code from the Book

Tablespace Sizes

select  Tablespace_Name, GB_Size, GB_Used, GB_Free,

                 Used_Perc "Used%", (100 - Used_Perc) "Free%"
from
(
select  Tablespace_Name, GB_Size,
            (GB_Size - GB_Free) GB_Used, GB_Free,
            (case when GB_Size <= 0 then 0 else
              round((GB_Size - GB_Free)*100/(GB_Size), 2) end )

      Used_Perc
from
(
select  a.Tablespace_Name, a.GB_Size, b.GB_Free
from   
            (
                        select d.TABLESPACE_NAME,
                        Round(sum(BYTES)/(1024*1024*1024), 2) GB_Size
                        from    dba_data_files d
                        group by TABLESPACE_NAME
            )
            a,
            (
                        select Tablespace_Name,
                         Round(sum(BYTES)/(1024*1024*1024), 2) GB_Free
                        from    dba_free_space
                        group by TABLESPACE_NAME
            )
            b
         where a.Tablespace_Name=b.Tablespace_Name
UNION ALL
select  a.Tablespace_Name, a.GB_Size, b.GB_Free
from
            (
                        select d.Tablespace_Name,
                        Round(sum(BYTES)/(1024*1024*1024), 2) GB_Size
                        from    dba_temp_files d
                        group by Tablespace_Name
            )
            a,
            (
                        select Tablespace_Name,
                         Round(sum(bytes_free)/(1024*1024*1024), 2) GB_Free
                        from    v$temp_space_header
                        group by Tablespace_Name
            )
            b
         where a.Tablespace_Name=b.Tablespace_Name
 order by 1
))

;