Tanel, That's a good idea. I briefly considered this, but didn't really dig into it.
The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > 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.net -- Author: Jared Still 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).
