Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: move table for chianed rows?

Re: move table for chianed rows?

From: NITIN <mahima.nitin_at_gmail.com>
Date: 26 Mar 2007 07:51:37 -0700
Message-ID: <1174920697.616040.28860@l75g2000hse.googlegroups.com>


On Mar 26, 10:25 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Mar 26, 7:11 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
> > Hi,
>
> > sorry for my question (i'm a newbie DBA) but i've read on forums that
> > for resolve chained rows on tables, it is enough move table in this
> > method:
>
> > alter table FOO move;
>
> > is true? but what does it do exactly this command??
>
> > thanks for any support!
>
> > bye
> > andrew
>
> Actually a chained row is a row to big to fit into a single Oracle
> block and as such rebuilding the table will not eliminate chained
> rows.
>
> Unfortunately Oracle places the count of migrated rows in the
> dba_tables.chain.cnt column. A migrated row is a row that after an
> update operation would no longer fit back into the block it was in so
> Oracle migrates the row to a new block leaving a rowid marker behind
> that points to the new row location. Accesses via an index go to the
> old location and then have to do an extra IO to find the row in its
> new location.
>
> Before rebuilding a table to eliminate chained rows you need to look
> at the average row length and make sure it is less than the available
> space within a block. Otherwise you would be wasting your time.
>
> What alter table move does is fully documented in the manuals, but
> basically it just copies the table to a new allocation which by
> default is in the same tablespace and uses the same storage
> information. As it copies the rows it will repack the blocks up to
> pctfree potentially resulting in the use of less space. Any space
> savings will depend on the past DML activity against the table.
> Migrated rows will be unmigrated. All indexes will be invalid after a
> move so the indexes have to be rebuilt. (Alter index rebuild or drop/
> create).
>
> HTH -- Mark D Powell --

Hi Mark

You said " As it copies the rows it will repack the blocks up to
> pctfree potentially resulting in the use of less space. Any space
> savings will depend on the past DML activity against the table.
> Migrated rows will be unmigrated. "

Can you please explain this better

thanks,
Nitin Received on Mon Mar 26 2007 - 09:51:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US