how to monitor tablespaces using sql

I usually monitor tablespaces using Toad, it has a nice graphical way of showing the tablespaces in DATABASE -> ADMINISTER -> TABLESPACES menu, and SCHEMA BROWSER ->TABLESPACES  to get a better view at the datafiles.

This is a different way of monitoring tablespaces. The following sql shows a complete report of the tablespaces of your database ordered by the free space percentage:

select  sel1.*
          , sel2. autoextensible
from(
SELECT a.tablespace_name,
       ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,
       ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free,
       ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)
          megs_used,
       ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       ROUND (maxbytes / 1048576) MAX
 FROM (  SELECT f.tablespace_name,
                 SUM (f.bytes) bytes_alloc,
                 SUM (
                    DECODE (f.autoextensible,
                            'YES', f.maxbytes,
                            'NO', f.bytes))
                    maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name,
         ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
         ROUND (
            SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
            / 1048576)
            megs_free,
         ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
         ROUND (
            (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
             / SUM (h.bytes_used + h.bytes_free))
            * 100)
            Pct_Free,
         100
         - ROUND (
              (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
               / SUM (h.bytes_used + h.bytes_free))
              * 100)
            pct_used,
         ROUND (SUM (f.maxbytes) / 1048576) MAX
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
) sel1,
( select max(autoextensible) autoextensible, tablespace_name from dba_data_files  group by tablespace_name
          union all
           select max(autoextensible) autoextensible, tablespace_name from dba_temp_files  group by tablespace_name) sel2
where sel1.tablespace_name= sel2.tablespace_name
ORDER BY 5 asc, max desc

You can find this sql and many other useful ones on this post: http://oracle-core-dba.blogspot.com/2008/01/cool-scripts-for-daily-dba-activities.html.



If you want next to see details of a certain tablespace you can use this sql:


select df.TABLESPACE_NAME, df.FILE_NAME, df.FILE_ID, df.STATUS, df.ONLINE_STATUS ,df.AUTOEXTENSIBLE , DF.USER_BYTES MAX_BYTES, SUM (fS.bytes) BYTES_FREE
 from dba_data_files df, dba_free_space fs
 WHERE df.tablespace_name = fs.tablespace_name(+)
 and df.file_id=fs.file_id(+)
 and df.TABLESPACE_NAME='USERS'
 GROUP BY df.TABLESPACE_NAME, df.FILE_NAME, df.FILE_ID, df.STATUS, df.ONLINE_STATUS ,df.AUTOEXTENSIBLE , DF.USER_BYTES

This is a handy way to monitor your tablespaces especially if you want to build a report concerning the tablespaces and datafiles.