Another way would be to do exchange partition between a single- or multipartition partitioned table and a regular table.
Easier than dbms_redefinition and less locking issues than with manual lock & swap. Tanel. --- Saatja: Rachel Carmichael <[EMAIL PROTECTED]> Kuupäev: 09.01.2004 16:14:33 --- > yes a few seconds. that's why I said "without locking for any length > of > time" and not "doesn't lock at all" :) > > The point being that you can do the redef and still allow access to > the > main table. Do a sync every once in a while while there is high volume > traffic, then do the finish when there is low volume > > if you manually lock the table, then do the copy, it can take > significantly more time > > > --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > > I thought that finish_redef_table does lock table for a few seconds? > > On 01/09/2004 07:54:26 AM, Rachel Carmichael wrote: > > > Chris, > > > > > > Have you considered using dbms_redefinition for your second case? > > That > > > would allow you to reorg and swap the tables without locking for > > any > > > length of time. > > > > > > Rachel > > > > > > > > > --- [EMAIL PROTECTED] wrote: > > > > Richard, > > > > > > > > I agree there are a number of reasons for reorganising tables. > > LMTs > > > > remove the > > > > need to reorganise a tablespace but not to reorganise a table. > > Two > > > > further real- > > > > ilfe examples of table reorgs: > > > > > > > > 1) The purge programs have at last been written and run deleting > > data > > > > > 2 years > > > > old. The system's been running for 4 years. So in simple terms > > most > > > > of the > > > > tables are approx 50% empty. You need to reorg in this case. > > > > > > > > 2) A "transaction log" table is inserted to throughout the day > > and > > > > most of the > > > > night. A clear down processing job runs at the end of the day > and > > > > deletes all > > > > the rows its processed, but more rows are being added. So the > > table > > > > is now < 1% > > > > full. Not good for FTS. So instead of a conventional reorg we > > > > implemented a > > > > nightly "table-swap". This meant locking the source table, > > copying > > > > it's > > > > contents to a replica empty single extent table, target table. > > The > > > > names of the > > > > target and source tables are swapped, hence "table-swap". The > new > > > > source table > > > > is now available to the application and the original source is > > > > truncated and > > > > ready to be the target in 24 hrs time. > > > > > > > > Cheers, > > > > > > > > Chris Dunscombe > > > > > > > > > > > > > > > > Quoting Richard Foote <[EMAIL PROTECTED]>: > > > > > > > > > MessageHi Thomas, > > > > > > > > > > Never say never (oh bugger, I've just gone and done it > myself). > > > > > > > > > > A large table accessed via a FTS for various important > > reporting > > > > requirements > > > > > has permanently shrunk in size from 10G to 100M (say list of > > > > Informix > > > > > customers ;) > > > > > > > > > > Business requirements have changed and you need to add some > > columns > > > > to a > > > > > table resulting in mucho row migration. > > > > > > > > > > You were told (incorrectly) that rows would grow significantly > > > > after loading > > > > > (honestly) but now the 80 pctfree value you've set is causing > > > > problems for > > > > > other really important reports. > > > > > > > > > > There are of course other cases but you get my point ;) > > > > > > > > > > Cheers > > > > > > > > > > Richard > > > > > ----- Original Message ----- > > > > > From: Mercadante, Thomas F > > > > > To: Multiple recipients of list ORACLE-L > > > > > Sent: Thursday, January 08, 2004 6:34 AM > > > > > Subject: RE: table reorganizations > > > > > > > > > > > > > > > Jolene, > > > > > > > > > > Tables should never *need* to be reorganized. This is an > old > > > > falacy. If > > > > > you know how big a table is going to grow, say in a year, then > > > > place it in a > > > > > Locally Managed tablespace with extent sizes to hold enough > > data > > > > for one year > > > > > (say 1M). > > > > > > > > > > You should never have to reorganize a table. > > > > > > > > > > Tom Mercadante > > > > > Oracle Certified Professional > > > > > -----Original Message----- > > > > > From: Shrake, Jolene [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, January 07, 2004 2:39 PM > > > > > To: Multiple recipients of list ORACLE-L > > > > > Subject: table reorganizations > > > > > > > > > > > > > > > What SQL statement do you use to identify tables that need > > > > > reorganization? > > > > > > > > > > How do you identify tables that are used in full table > > scans? > > > > How often > > > > > do you run this query? > > > > > > > > > > Thanks, > > > > > Jolene > > > > > > > > > > > > > > > > > Chris Dunscombe > > > > > > > > [EMAIL PROTECTED] > > > > > > > > ------------------------------------------------- > > > > Everyone should have http://www.freedom2surf.net/ > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: > > > > 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). > > > > > > > > > __________________________________ > > > Do you Yahoo!? > > > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes > > > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Rachel Carmichael > > > 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). > > > > > > > -- > > Mladen Gogala > > Oracle DBA > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Mladen Gogala > > 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). > > > __________________________________ > Do you Yahoo!? > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes > http://hotjobs.sweepstakes.yahoo.com/signingbonus > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > 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). > >