Re: dbms_server_alert.tablespace_byt_free measures really KB?
From: onedbguru <onedbguru_at_yahoo.com>
Date: Fri, 11 Feb 2011 15:29:33 -0800 (PST)
Message-ID: <5e96dffa-3720-45a7-9ec6-007ecde3e334_at_x11g2000yqc.googlegroups.com>
On Feb 11, 3:17 am, Lothar Armbrüster
<l.armbrues..._at_vertriebsunion.de> wrote:
> On 11 Feb., 03:39, onedbguru <onedbg..._at_yahoo.com> 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.
>
> Regards,
> Lothar
Date: Fri, 11 Feb 2011 15:29:33 -0800 (PST)
Message-ID: <5e96dffa-3720-45a7-9ec6-007ecde3e334_at_x11g2000yqc.googlegroups.com>
On Feb 11, 3:17 am, Lothar Armbrüster
<l.armbrues..._at_vertriebsunion.de> wrote:
> On 11 Feb., 03:39, onedbguru <onedbg..._at_yahoo.com> 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.
>
> Regards,
> Lothar
The problem is percentages.. I had a db at one time where the tablespace was 95% full with 300GB free and at approximately 5G/day added, well you do the math... :) Received on Fri Feb 11 2011 - 17:29:33 CST