Re: Tablespace free space monitoring, including AUTOEXTEND

  • From: David Fitzjarrell <oratune@xxxxxxxxx>
  • To: avramil@xxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 2 Jul 2010 12:54:27 -0700 (PDT)

This will return sufficient data to monitor autoextend tablespaces; use the 
adj_pct_free column for alerts:

set linesize 132
with used_free as(
select d.tablespace_name,
       nvl(sum(f.bytes)/1024/1024, 0) mb_free,
       sum(d.bytes)/1024/1024 mb_curr,
       case when sum(d.maxbytes)>0 then sum(d.maxbytes)/1024/1024
            else  sum(d.bytes)/1024/1024 end mb_total
from dba_data_files d left outer join dba_free_space f
on (f.tablespace_name = d.tablespace_name)
group by d.tablespace_name
)
select tablespace_name,
       mb_free, mb_curr, mb_total,
       round((mb_free/mb_curr)*100,0) curr_pct_free,
       round(((mb_free+mb_total-mb_curr)/mb_total)*100,0) adj_pct_free,
       mb_total - mb_curr mb_unalloc
from used_free
/

 
David Fitzjarrell





________________________________
From: Lou Avrami <avramil@xxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Fri, July 2, 2010 3:14:11 PM
Subject: Tablespace free space monitoring, including AUTOEXTEND

Hi folks,

The OpenView team here where I work currently has just turned on database 
monitoring for several 10.2.0.4 production databases, built by a vendor.  This 
application creates and drops tablespaces as part of its regular operations.

The OpenView tablespace monitoring is periodically reporting that tablespaces 
in 
these databases are near 100% capacity, even though that isn't the case.  For 
example, it's reporting that a 2 GB tablespace is 99% full, even though the 
tablespace has the capacity to expand to 4 GB.

Does anyone have SQL handy that reports on tablespaces that are "almost" full, 
and that also takes into account tablespaces that autoextend?

Lou Avrami
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2010