Re: anyone have a script to find row migration?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ATcxa.35686$1s1.518935_at_newsfeeds.bigpond.com...
>
> "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
>
>
>

thanks Howard. Received on Fri May 16 2003 - 17:41:09 CDT