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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
