Re: dbms_server_alert.tablespace_byt_free measures really KB?

From: onedbguru <>
Date: Thu, 10 Feb 2011 18:39:22 -0800 (PST)
Message-ID: <>

On Feb 10, 10:27 am, Lothar Armbrüster
<> 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 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.

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

Original text of this message