Re: Tablespace free space monitoring, including AUTOEXTEND

  • From: "Leyi Zhang (Kamus)" <kamusis@xxxxxxxxx>
  • To: oratune@xxxxxxxxx
  • Date: Tue, 13 Jul 2010 17:22:31 +0800

Hi, David

Your script's result looks beautiful, but there is a bug in your
script will lead to wrong result.

You used:
from dba_data_files d left outer join dba_free_space f
on (f.tablespace_name = d.tablespace_name)

If there are more than 1 free space showed in dba_free_space for some
tablespaces, your script will over calculate bytes and maxbytes.

In our server, SYSAUX tablespace has 11 free spaces.
SQL> select TABLESPACE_NAME,BYTES/1024/1204 from dba_free_space where
tablespace_name='SYSAUX';

TABLESPACE_NAME                BYTES/1024/1204
------------------------------ ---------------
SYSAUX                              .159468439
SYSAUX                              .053156146
SYSAUX                              .318936877
SYSAUX                              .318936877
SYSAUX                              .850498339
SYSAUX                               2.7641196
SYSAUX                               1.3820598
SYSAUX                               2.0730897
SYSAUX                              1.70099668
SYSAUX                              3.40199336
SYSAUX                               7.6013289

11 rows selected.

-- Your script will show SYSAUX tablespace has 360447.828MB total.
SQL> select d.tablespace_name,
  2         nvl(sum(f.bytes)/1024/1024, 0) mb_free,
  3         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
  4    5    6  from dba_data_files d left outer join dba_free_space f
  7  on (f.tablespace_name = d.tablespace_name)
  8  where d.tablespace_name='SYSAUX'
  9  group by d.tablespace_name
 10  ;

TABLESPACE_NAME                   MB_FREE    MB_CURR   MB_TOTAL
------------------------------ ---------- ---------- ----------
SYSAUX                              24.25       5720 360447.828

-- But actually it only has 32767.9844MB total.
SQL> select sum(d.maxbytes)/1024/1024 from dba_data_files d where
d.tablespace_name='SYSAUX';

SUM(D.MAXBYTES)/1024/1024
-------------------------
               32767.9844

-- This is because "left outer join", over calculate 11 times.
SQL> select  32767.9844*11 from dual;

32767.9844*11
-------------
   360447.828


--
Kamus <kamusis@xxxxxxxxx>

Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org



On Sat, Jul 3, 2010 at 3:54 AM, David Fitzjarrell <oratune@xxxxxxxxx> wrote:
> 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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l

Other related posts:

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