RE: Defragmentation of tablespace

After I posted what I did, it occurred to me that somebody might think I was actually trying to give detailed description of how to do it. What I intended was to present enough of it to make one pause and ask if it is really, truly necessary and work to prevent it from happening again. If you have a database of a significant degree of complexity, trying to do things a table at a time can get you up to your anus in integrity constraints and invalid objects in a hurry.

Usually (let's be vague, OK?) tables in a production database go one way -- bigger; and you don't go around dropping a recreating tables in a production database (do you?). So you shouldn't be getting fragmentation there. But rebuilding indexes in a single tablespace can make a mess. If you need to do that, rebuild them to an alternate tablespace. Then, if you absolutely must, rebuild them back to the original tablespace after coalescing the space there.

In a development environment, the export - drop objects - coalesce - import method is probably the least troublesome.

> -----Original Message-----
>
> Check out the paper "How to stop defragmenting and start living". It
> changed my life many years ago. Well, my life as a DBA anyway :)
>
> Jay Miller
>
> -----Original Message-----
> Sent: Thursday, October 31, 2002 11:29 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> > -----Original Message-----
> >
> > Does anybody help me to defrag / reorganise the user
> tablespaces which
> > is large in size.
> > Is there any script for that ? If available can somebody
> send it to me
> > ?
> >
>
> The two methods that come to mind are:
>
> 1. Export the objects in the tablespace; drop them from the
> tablespace;
> coalesce the table tablespace; import the objects back in.
> If there are
> relationships between objects in this tablespace and those in another
> tablespace, it might be easier to export the entire schema, drop the
> schema's objects, then import the schema after coalescing all
> free space.
>
> 2. Create a new tablespace (you might need to grant quota for
> a schema);
> move tables to that tablespace (which will require rebuilding
> any indexes on
> those tables after you are done); AND/OR rebuild indexes to
> that tablespace.
> If you are moving only indexes, you can do that online. You
> can move tables
> without taking down the database, but all the indexes go
> invalid when you
> move the table; so any DML activity will probably get
> clobbered. I suppose,
> if you wanted to go to the trouble, you could keep the
> database active by
> dropping indexes on any tables to be moved, then re-create
> the indexes after
> the tables were moved. This would allow at least some DML activity to
> continue -- at a reduced level of performance. Then move
> everything back to
> the original tablespace after coalescing if you need for the
> objects to
> reside in the original tablespace. (All the online stuff assumes your
> version of Oracle is recent enough to support it.)
>
> Before going to all this trouble, try coalescing the free space in the
> tablespace, then look to see if you still have chunks of free space
> scattered all over the place, all of them smaller than you
> need, but the sum
> of which is considerable.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephen Lee
> INET: slee_at_dollar.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephen Lee
  INET: slee_at_dollar.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).