RE: segment fragmentation

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <thump@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 8 Sep 2004 17:06:59 -0400


As Ryan mentioned, LMTs prevent fragmentation, if you have uniform =
extents, or at least mitigate it, if you have autoallocate extents.  =
However, if you delete a significant amount of data from a table, then, =
there could be some impact due to high-water mark.  Consider, however, =
when the high-water mark is referenced.  For a table, only on full table =
scan.  For indexes on the table, only on an index fast full scan.  So, =
if your queries don't utilize those access paths, then you really don't =
need to concern yourself w/ HWM.

You don't mention what version you're on.  If HWM is an issue, you don't =
need to resort to export/import, if you're at least at 9i.  You can =
ALTER TABLE table_name move; to reorganize the table data.  Note that =
when that's done, all indexes will be invalidated and will need to be =
rebuilt.  Consider the possibility of NOLOGGING, but make sure you fully =
understand the recovery implications before proceeding there.  Also, I'm =
not really a 10g guy, yet, but in 10g, there's a 'shrink' clause to =
ALTER TABLE that (from what I've read) does a nice job, maintains the =
object on-line availability and maintains indexes at the same time.  =
But, I haven't used that....

Hope that helps,


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David
Sent: Wednesday, September 08, 2004 3:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: segment fragmentation

I have been asked to determine segment/object fragmentation levels after =
mass delete has been performed.

Do LMT only negate fragmentation occuring at a tablespace level as an
issue and segment fragmentation and high water mark issues are still
relevant after a mass purge?

How does one check for this type of fragmentation?

It has been requested I perform an export/import of said schema after
verifying segment fragmentation exists.

By the way, I have finally purchased Optimizing Oracle Performance and
have begun the reading.


To unsubscribe - =
To search the archives -
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe 
To search the archives -

Other related posts: