RE: Weird question
Date: Sun, 23 Oct 2022 08:56:26 -0400
Message-ID: <0a6401d8e6de$deda5b50$9c8f11f0$_at_rsiz.com>
Before I resorted to their solution I would try force reading the table through SGA (as opposed to PGA). Referencing old rollback that is not actually needed to get a current block version (not being actually needed is implied by their solution suggestion that a CTAS would work) sounds like an artifact similar to delayed block cleanout (which I am supposing discarding the old undo happened during the upgrade). IF that is done into the PGA the problem is not corrected even though individual current blocks may be produced, but the “newer” version (current) of the block *should* be written back if the read is through the SGA. IF CTAS can select valid current block versions, I don’t understand why a full table scan cannot. CTAS has no magical ability to create data that I am aware of (and it shouldn’t.)
I *thought* there was a hint to force an FTE though the SGA, but I can’t put my hands on it. IF you read all the block addresses restricted to one row per block into a new table and read the table with the problem via referencing the row block addresses table, that should force the read via the SGA and clean out all the blocks of the table except for actually empty blocks. Perhaps someone will chime in with that ?hint?.
I *think* that should “touch” all the blocks that need to be touched, but I don’t have a “broken” database to test it on and I have no idea how to simulate your exact situation.
Good luck!
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor
Sent: Sunday, October 23, 2022 2:17 AM
Is there any way to force Oracle to "touch" every block in a table and update/refresh the SCN without running an update/delete?
Does an update/delete followed by a rollback cause the SCN to advance, or does the rollback put the SCN back as well? (I'm assuming it requires a commit to advance the SCN)
THe genesis of this question comes from a situation similar to this:
Flashback Version Query Fails With Error - ORA-01555 After 18c Upgrade (Doc ID 2596214.1)
SOLUTION
Run CTAS of each table to generate new block scns for each block.
Making the block scn current will stop the ORA-1555 errors.
To: oracle-l_at_freelists.org
Subject: Weird question
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 23 2022 - 14:56:26 CEST