On 15/07/11 17:46, Norman Dunbar wrote:
No! No! No! No! No!
I shall explain, perhaps it would make more sense.
Consider monitoring a tablespace, you wish to write a script that will
alert you at say 80% with a warning, and a critical at 90%. Sounds easy.
Ok, consider that your tablespace may have multiple files. Still easy.
Add in the fact that the tablespace may be in AUTOEXTEND mode. Not quite
so easy now is it? 80% or 90% or what figure do we consider now?
Considet too the maximum size allowed, it may be an explicit limit or
UNLIMITED. This too should be considered as part of the query.
Still think it’s simple? Add in the fact that some files making up the
tablespace are in AUTOEXTEND while some others are not. Getting harder
to write that one SQL statement isn’t it?
Of course, we only wish to monitor tablespaces with PERMANENT contents,
so the query is getting more difficult now.
However, consider those huge tablespaces where a 10% free space is still
potentially gigabytes in size, so in those tablespaces, we only want to
be alerted with a critical alert when the free space drops below a
certain number of megabytes. Still think it’s easy?
And we don’t want to monitor TEMP or UNDO as we monitor those separately.
Did I mention that there are times when the actual size of the data
file, in AUTOEXTEND mode, is actually BIGGER that the maximum size
specified? Factor that in to the query!
I wrote the above SQL query for Nagios monitoring of my databases, and I
really don’t advise it!
And finally, how exactly do you factor in the case where the tablespace
is allowed to grow to a given size (or UNLIMITED) yet the actual disc(s)
it is sitting on have insufficient space to allow it to grow to the
allowed maximum size?
AUTOEXTEND is “useful” when you have an overnight job running that
“might” use up all the space, so you don’t want the on-call DBA called
out, so that’s the time when AUTOEXTEND is helpful. Other than that, the
DBA should know how much space is left.
Other opinions are available, as ever.
For homework, I want you all to write the SQL query required to monitor
a tablespace given all of the above - except the fact that you can’t
(couldn’t at the time) extract the physical disc size left on the
PL/SQL is not permitted, neither is any other external language, only
Have it on my desk, Monday morning first thing!