Re: anyone have a script to find row migration?

"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 Received on Fri May 16 2003 - 16:10:32 CDT