Re: Migrated Rows

Hi Norman,

Since the only fix for row chaining is to increase your block size, I shall be *extremely* interested to hear how you've pulled that trick off!

If CHAIN_CNT in DBA_TABLES is anything other than 0, you have either row chaining or row migration. Chaining you can do sod all about, unless you re-create your database with a bigger block size (and even that won't fix up a 2Gb video file being chained!). Therefore, for all tables with a non-zero chain_cnt, check the table structure. If the length of a single row can't fit into a block, then the CHAIN_CNT figure is row chaining. If rows *can* fit into a single block, but chain_cnt is still non-zero, it's row migration -and a 'move tablespace' will quickly fix that up.

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================


"Norman Dunbar" <ndunbar_at_lynxfinancialsystems.co.uk> wrote in message
news:F43E6BAE5BB5D411A44C00805FBE740DA14FD4_at_apps.leeds.lfs.co.uk...


> Howard,

>

> Yeah, I've analysed with compute stats on all the tables and I've

> detected the chained rows ok. I'm led to believe that the V$SYSSTAT view

> tells me that I have migrated rows. I can easily identify and unchain

> the chained ones, but I wanted to know how to identify the migrated ones

> - none of the manuals seem to say how.

>

> However, it would apear that if I fix the chained rows, I'll (probably)

> fix the migrated ones as well, so no worries - apart from corectly

> determining the PCTFREE/PCTUSED stuff which someone (not me !) has set

> to 1% and 90% - which implies that these tables don't update or delete

> rows very often/at all.

>

> Cheers,

> Norman.

>

> -----Original Message-----

> From: Howard J. Rogers [mailto:howardjr_at_www.com]

> Posted At: Thursday, June 14, 2001 10:26 PM

> Posted To: server

> Conversation: Migrated Rows

> Subject: Re: Migrated Rows

>

>

> Assuming you have calculated statistics on your tables in the recent

> past, a

> simple select table_name from dba_tables where chain_cnt <> 0 would do

> the

> trick.  That of course will also pick up any tables that have row

> chaining

> as well as row migration, but at least it would be a start.

>

> But I suspect you want something a bit cleverer than that.... in which

> case,

> er... I'm at a loss.

>