Re: anyone have a script to find row migration?

"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. Received on Fri May 16 2003 - 16:29:06 CDT