RE: segment fragmentation

David,

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,

-Mark

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Sent: Wednesday, September 08, 2004 3:58 PM To: oracle-l_at_freelists.org
Subject: segment fragmentation

I have been asked to determine segment/object fragmentation levels after = a
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.

Regards,
--=20

..
David

--

To unsubscribe - =
mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20 To search the archives - http://www.freelists.org/archives/oracle-l/
--

To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ Received on Wed Sep 08 2004 - 16:02:43 CDT