Re: Tablespace free space monitoring, including AUTOEXTEND

From: Mark Brinsmead <pythianbrinsmead_at_gmail.com>
Date: Mon, 12 Jul 2010 21:55:37 -0400
Message-ID: <AANLkTimroEGg4m7kf7YWusXTrLKd-IOMwAFj3nZ6jhRX_at_mail.gmail.com>

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_at_gmail.com> 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_at_concentric.net>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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 12 2010 - 20:55:37 CDT