RE: Tbs READ ONLY and Snapshot too old

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Fri, 18 Jun 2004 09:22:29 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE20149CA51@na1fcm60.dearborn.ford.com>

As a test, (if the OP is still reading, and my time sequence is spot on), try computing all statistics on all objects in the tablespace (not estimate, full compute). This will visit every block on all tables and indexes (if any are in the tablespace) and should clean out all the blocks.

[BL] Much easier to simply do "select max(column)..." on an un-indexed column to cause FTS to cause cleanouts. Statistics is a bit of an overkill.

[PWS] Have tried that. Didn't work. Turned out that Oracle was using an index to supply the needed info for one step in its execution plan, and _those_ were the blocks which needed to be cleaned out. So I went into overkill mode.

As I mentioned when I first jumped into this converation, I've been running into this off and on for better than 7 years. If there is a better way to prevent it I would love to hear about it (corporate policy makes the obvious solution (buy enough disk to size the undo to handle any possible situation) unavailable).

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



application/ms-tnef attachment: winmail.dat

Received on Fri Jun 18 2004 - 08:19:49 CDT