Re: dbms_server_alert.tablespace_byt_free measures really KB?
Date: Thu, 10 Feb 2011 18:39:22 -0800 (PST)
Message-ID: <3bf75c10-459c-4b46-bd69-6adadb4de781_at_x1g2000yqb.googlegroups.com>
On Feb 10, 10:27 am, Lothar Armbrüster
<l.armbrues..._at_vertriebsunion.de> wrote:
> Hello out there,
> I tried to configure alerts for full tablespaces using
> dbms_server_alert.set_threshold. Since we do not have licensed
> diagnostics pack I cannot do this with dbconsole. I have to resort to
> the command line.
> Anyway, I want to get a warning when the tablespace has less than 1GB
> free space.
> So I issued the following command:
>
> begin
> dbms_server_alert.set_threshold(metrics_id =>
> dbms_server_alert.tablespace_byt_free,
> warning_operator =>
> dbms_server_alert.operator_le,
> warning_value => '1048576',
> critical_operator =>
> dbms_server_alert.operator_le,
> critical_value => '204800',
> observation_period => 1,
> consecutive_occurrences => 1,
> instance_name => null,
> object_type =>
> dbms_server_alert.object_type_tablespace,
> object_name => 'TS_TABLES');
> end;
> /
>
> But now I get a critical warning saying tablespace TS_TABLES only has
> 8505 MB free space left.
> Therefor the question is: are the units of
> dbms_server_alert.tablespace_byt_free really KB as described in the
> documentation or are this MB as I suspect?
> Setting the thresholds to 1024 and 200 the alerts disappear but I want
> to be sure that I get warnings if there are fewer than 1GB left and
> not fewer than 1MB. In the latter case there would not be enough time
> to react. :-)
>
> This is Oracle 10.2.0.5 on Windows 2000.
>
> Regards,
> Lothar
CAVEATE: This does post not solve the immediate problem but may help you in the long run - as jg said - this is a bug - see MOS Bug 5569515
Just curious, but are you doing any cleanup, archiving etc or just letting it continue to grow? Database archiving and purging seems to be overlooked in a LOT of shops. I recently reviewed a db that purged a lot of data to recover space, however, because of the way that LMT works, the space is not recovered until one does a SHRINK on the objects within that tablespace. If one understands that once LMT tablespace segments are allocated to an object they remain allocated to that object until you "enable row movement then alter ... shrink or alter ... shrink cascade", then you can make plans to clean them up. This is especially true if the old, purged data contained very large *LOBS. The previous DBA did not fully understand this, and just kept adding more datafiles to the tablespace. When the "SHRINK" ran, they recovered more than half of the database size. Adding a purge job that runs daily - without doing the shrink, those objects are now holding at a constant number of segments. Segment allocation in LMT is not a cheap operation, so if an object has empty or unused space, it does not need to spend I/O and time to do that allocation.
As for indexes with "unused" space, you might just want to leave it that way as you add more data, it will more than likely just grow right back to where it is now and it may not be worth the resource usage to rebuild it.
One thing you might consider is a holistic methodology of managing
space - and not relying on the "OH CRAP!!!" methodology. Do the work
up front and it will save your sanity and just maybe your marriage/
relationship(s).
[you: Sorry <fill in term of endearment here>, I have to run to the
office and fix yet another space issue ... them: But you promised we
would <fill in couples activity here> tonight. ]
0) ensure you understand the database, the environment and application
to know how it currently uses space.
1) ensure you have done transactional rate analysis to determine rate
of growth both in row size and row quantity (actual and projected).
This is not always static, but is a very good start)
2) ensure you have sufficient disk space
3) ensure you have sufficient disk space (Yes, I put this in twice as
most places try to "skimp" on the disk space - DON'T SKIMP!!)
4) Develop a full-scope data retention plan (what to keep, what to
throw away, what to archive - where and how long)
5) ACTUALLY IMPLEMENT THE PLAN!!!
6) consider using ASM (even on Windows)
If this is done [mostly] correctly, you should very rarely need to worry about the monitoring portion. I did not say "don't do monitoring" - I did say you won't need to worry about it. With ASM, you also will not need to worry about data files or temp files or redo log files filling up causing your db to "suspend" until corrected as ASM will manage this for you. And if you use ASM AND have sufficient diskgroup space - you just may get to sleep more. Don't be afraid of ASM - it is your friend.
onedbguru
Famous last words heard in the back woods of W.Va "Hey Bubba, hold my
beer and watch this... "
Received on Thu Feb 10 2011 - 20:39:22 CST