Re: Sorting V$LOGMNR_CONTENTS to recreate long SQL statements.

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

--
http://www.freelists.org/webpage/oracle-l