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
- chris_at_thedunscombes.f2s.com 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 <richard.foote_at_bigpond.com>:
>
> > 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:JEShrake_at_Pella.com]
> > 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
>
> chris_at_thedunscombes.f2s.com
>
> -------------------------------------------------
> Everyone should have http://www.freedom2surf.net/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: chris_at_thedunscombes.f2s.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Fri Jan 09 2004 - 06:54:26 CST