Re: Full Oracle Block

wumutek_at_yahoo.com (Vincento Harris) wrote in message news:<2fa13ee7.0401150844.6bf0ce23_at_posting.google.com>...

> The initrans is 1 and the maxtrans is 255.Is it possible that the
> block is close to full and there is no room for creating a new ITL ?

Assuming like you said that pctfree is default, I'd say not likely. I'm also assuming updates change data but do not significantly increase the size of a row.

As usual it depends on the nature of the updates. It could also be caused by an update to a column that is indexed, in which case it could well be a problem in the respective index. Not the table.

Easiest way to make sure of no problems: rebuild the table (and indexes!) with initrans equal to the potential number of concurrent update transactions on each block. Note: not the potential number of updaters on each block: the max number of CONCURRENT actual updaters. Which will not exceed the max number of rows per block. Which you can get easily with either a rowid group query or stats. Or you could use average rather than max.

> As usual "nothing has changed"

Ain't it always the case? :)

> What would be the best way to investigate how full the block is?

Which block? Are you sure you wanna deal with issues at the block level? May I suggest a strategy approach on a more global base? The above approach will nearly eliminate all possible instances of problems with ITL in every block. Apart from some obscure and unusual conditions that may well not ever be the case in your db. At very little cost in space.

> 100% statistics are run nightly both on tables and indexes

Do you subscribe to oracle-l? There was a lengthy discussion there not long ago about why NOT to analize stats 100% every night... (nothing to do with this problem, though)

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Thu Jan 15 2004 - 19:45:59 CST