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