| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: table reorganizations
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
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 <wisernet100_at_yahoo.com> Kuupäev: 09.01.2004 16:14:33 ---Received on Fri Jan 09 2004 - 10:44:35 CST
> 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 <mladen_at_wangtrading.com> 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
> > >
> > >
> > > --- 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).
> > >
> >
> > --
> > Mladen Gogala
> > Oracle DBA
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mladen Gogala
> > INET: mladen_at_wangtrading.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).
>
>
--=_2ff565a0bcd8e1ee827b3c33709f4a2f-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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).
 |
 |