Re: Tablespace free space monitoring, including AUTOEXTEND

This looks pretty good, although I have not analyzed in great detail.  Two
(important) things that it seems to do right are:

   1. Consider MAXBYTES only when autoextend is actually enabled, and
   2. Recognize that MAXBYTES might be *less* than the actual size of the
   file, and deal with it properly.



On Fri, Jul 2, 2010 at 3:48 PM, Ravi Madabhushanam <
ravi.madabhushanam@xxxxxxxxx> wrote:

> I use the below one.. may not be the best.. but it works:
>
> WITH my_ddf AS
>     (
>         SELECT file_id, tablespace_name, file_name,
>                DECODE (autoextensible,
>                        'YES', GREATEST (BYTES, maxbytes),
>                        BYTES
>                       ) mysize,
>               DECODE (autoextensible,
>                       'YES', CASE
>                          WHEN (maxbytes &gt; BYTES)
>                             THEN (maxbytes - BYTES)
>                          ELSE 0
>                       END,
>                       0
>                      ) growth
>          FROM dba_data_files)
> SELECT&nbsp;&nbsp; my_ddf.tablespace_name,
>          ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
>          ROUND (SUM (growth) / (1024 * 1024)) growth,
>          ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
>          ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
>                ) totfree,
>          ROUND (&nbsp; (SUM (NVL (freebytes, 0)) + SUM (growth))
>                  / SUM (my_ddf.mysize)
>                  * 100
>                ) perc
>     FROM my_ddf, (SELECT&nbsp;&nbsp; file_id, SUM (BYTES) freebytes
>                       FROM dba_free_space
>                   GROUP BY file_id) dfs
>    WHERE my_ddf.file_id = dfs.file_id(+)
>          AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
> GROUP BY my_ddf.tablespace_name
> ORDER BY 6 DESC
>
> I've removed the UNDO tablespace from the query.. you may want to comment
> it out if needed.
>
> (
> http://oraworklog.wordpress.com/2010/02/23/free-space-in-auto-extensible-tablespaces/
> )
>
> Thanks,
> Ravi.M
>
> On Sat, Jul 3, 2010 at 12:44 AM, Lou Avrami <avramil@xxxxxxxxxxxxxx>wrote:
>
>> 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
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>


-- 
Cheers,
-- Mark Brinsmead
   Senior DBA,
   The Pythian Group
   http://www.pythian.com/blogs

Other related posts: