Re: dbms_server_alert.tablespace_byt_free measures really KB?

From: Lothar Armbrüster <>
Date: Fri, 11 Feb 2011 00:17:52 -0800 (PST)
Message-ID: <>

On 11 Feb., 03:39, onedbguru <> wrote:
> On Feb 10, 10:27 am, Lothar Armbrüster

> 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.

> onedbguru
> Famous last words heard in the back woods of W.Va "Hey Bubba, hold my
> beer and watch this... "

Hello onedbguru,
thank you for your detailed post. It contains some suggentions I will consider.
To satisfy your curiosity: I normally let the database grow, since most of the
data needs to be kept over longer periods of time. From time to time I increase
a datafile. I consider a growth of 2-3GB for two of my tablespaces per year normal
and expected growth. I checked the free space of these tablespaces regularly and
was slightly annoyed by dbconsole saying on tablespace is 97% full although
having over 1GB of free space. Recently I stumbled upon the dbms_server_alert
package and the posibility to set the threshold to an absolute value for free space.
So I see which tablespaces need care. Setting this caused me to run into the bug.

Lothar Received on Fri Feb 11 2011 - 02:17:52 CST

Original text of this message