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