Hi Rich,
I think this is a design flaw that you have to deal with, in the past I’ve
done similar to:
Select sql_undo
from (
Select t.*, rownum rn
From v$logmnr_contents
)
Where scn >95862215654
Order by scn desc, rn
V$logmnr_contents will return rows in the correct sequential order.
There might be better answers, but I hope this helps for now.
I assume the undo has been lost from undo tablespace, otherwise flashback
query/table would be useful. Or even the flashback_transaction_query view.
Thanks,
Andy
On Thu, 6 Jun 2019 at 19:43, Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
wrote:
Hey all,
In 12.1.0.2, we want to undo some archiving via DELETEs. Thinking it
would be easier to log mine SQL_UNDO, I grabbed the SQLs using:
SELECT
sql_undo
FROM v$logmnr_contents
WHERE
scn > 95862215654
ORDER BY scn DESC, rs_id DESC, csf DESC
The problem is when there are more than 2 rows returned to recreate a
single statement, like a row containing a BLOB. In this case, "csf" is
only a "0" or "1" instead of a true sequence. So all segments of the
SQL_UNDO except the last have a "csf" value of "1". My SQL above does not
consistently return the rows in the correct order, but I don't see any
column that contains values I can use to re-sequence the SQL_UNDO values
back together. Seeing as this query returns >1M rows, it's not something
I'll be doing by hand.
Is this just a design flaw or am I missing something?
TIA,
Rich
