Re: Sorting V$LOGMNR_CONTENTS to recreate long SQL statements.
- From: Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 07 Jun 2019 07:41:30 -0500
On 2019/06/06 14:02, Andy Sayer wrote:
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.
I still can't see how the order is guaranteed with your example. The
only way to know is to validate every row that was stitched together,
which would be a time-consuming process.
And yes the undo has unfortunately been aged out. I think I've
exhausted the "easy" database solutions now. We'll have to rely on
"unarchiving" to rebuild those tables. At least this is in a test
environment. :)
Thanks for the feedback!
Rich
Other related posts: