Re: Defrag Tablespaces ? How ?

From: John P. Higgins <jh33378_at_deere.com>
Date: Wed, 14 Oct 1998 20:35:27 -0500
Message-ID: <3625515F.D0E051CF@deere.com>

And even better, prevent future problems!

Free extents are are primarily caused by dropping segments. Every segment extent becomes a free extent., ready for re-use by a new extent.

Make all extents of all objects in a tablespace one size. This means INITIAL and NEXT, as well as PCTINCREASE = 0. This lets every new extent fit into any free extent. This will cause some objects to have multiple extents. Do NOT worry about this, just make sure the extent size is a multiple of 64K (based on the DB_FILE_MULTIBLOCK_READ_COUNT parameter).

NEVER use exp with COMPRESS=Y; this is future fragmentation.

By the way, it is better and faster to have a tablespace's freespace divided up into standard extents. If all the freespace was coalesced into one freespace extent, Oracle would have to take the time to break it into pieces to create a new object extent. So DO set the tablespace's default pctincrease to 0 to prevent SMON from wasting time coalescing your properly sized free space extents.

Different tablespaces can use different common sizes.

If you cannot live with a single extent size in a tablespace, at least make every extent size a power-of-two times 64K and keep as many as possible at a standard size.

satar@my-dejanews.com wrote:

I looked into Platinum, it doesn't defrag a tablespace. The only way to
defrag a tablespace has already been stated (export objects & import objects
& coalesce). If you're in a 24x7 enviroment, you're screwed. I would create a
standby database (configuired properly with all tables in one extent, and the
tablespaces looking good) and switch the production onto the standby. Fix the
production, then switch back (not necessary).

Satar
In article <36248C1A.7E5E3802@worldonline.nl>,
  Ton Raaijmakers <torgan@worldonline.nl> wrote:
> You can reorg a tablespace but you have to buy a utility.
> Tsreorg from Platinum can do this.
>
> Ton Raaijmakers
>
> Asko Tiidumaa wrote:
>
> > > you can't export a tablespace, you can only export all tables in a
> > > tablespace, indexes you must drop and recreate them (Release < 7.3) ...
> > > Then you must drop the tablespace and recreate it or
> > > set default storage (pctincrease 1) for it ( not 0) so that SMON
> > > coalesces all free extents or
> > > in Oracle release greater 7.3 "alter tablespace USERS coalesce;"
> >
> > but 24*7 availability?
> >
> > Asko Tiidumaa
>
>

--
Oracle DBA/UNIX System Admin
Advanced Enterprise Solutions
(949) 756-0588
Oracle Re-Seller

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

  Received on Wed Oct 14 1998 - 20:35:27 CDT