RE: Defragmentation of tablespace

Also, if you do move to a new tablespace please consider moving to uniform extents which will remove any need to defragment in the future (I assume you're doing it because of wasted space in the tablespace and not because of the now-discredited notion that it's bad to have many extents for a particular segment?).

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).