Re: anyone have a script to find row migration?

"Ryan" <rgaffuri_at_cox.net> wrote in message news:CKcxa.18822$823.2592_at_news1.east.cox.net...
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:9icxa.35677$1s1.516069_at_newsfeeds.bigpond.com...
> >
> > "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
> > news:1efdad5b.0305161203.60a972cf_at_posting.google.com...
> > > anyone have a query to find row migration? There is one on ixora, but
> > > I havent been able to get views for the x$ tables yet and I dont have
> > > the sys password.
> > >
> > > i think there are other queries I can use.
> > >
> > > anyone got a quick script or a site i can check
> >
> > Hang on... what do you want to do exactly?
> >
> > If you just want to know whether row migration has occurred, compute
> > statistics for a table, and check the CHAIN_CNT column in dba_tables. If
> > there's a number there, you have either row migration or row chaining.
To
> > distinguish between the two, have a look where AVG_ROW_LEN in the same
> view
> > is bigger or smaller than BLOCK_SIZE (from dba_tablespaces). If it's
> bigger,
> > you have chaining. If it's smaller, you have migration.
> >
> > If you mean 'how do I identify which rows exactly have migrated', just
run
> > the script utlchain.sql (in ye ever handy ORACLE_HOME/rdbms/admin
> directory)
> > to create a table called 'chained_rows', and then do 'analyze table BLAH
> > list chained rows'. If you select * from chained_rows after that, you
will
> > have the head rowids for each of the migrated rows. If you include a
> > subselect from the main table (something like 'select * from
chained_rows
> > where head_rowid in (select * from BLAH)' ) then you can see the data
> that's
> > involved.
> >
> > Does that not do what you're after?
> >
> > Regards
> > HJR
> >
> >
>
> thankls howard. I thought the CHAINED_ROWS table was for chained rows. not
> migrated rows.
>
> thanks.

No, not really. It's for identifying anything where part of a row in one block points to part of the row in another. That applies to both chaining and migration... though, as you obviously know by now, you can fix up migration sort-of-relatively-easily, but chaining you live with (unless you want to dabble with different block sizes in 9i, or create a brand new database with a bigger block size... neither of which are particularly attractive cures!)

Regards
HJR Received on Fri May 16 2003 - 16:50:36 CDT