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

From: Andy Sayer <andysayer_at_gmail.com>
Date: Thu, 6 Jun 2019 20:02:08 +0100
Message-ID: <CACj1VR4A9Z0P6FiB1__4SckfVJVzSsuxsQdweAHWBoB8_Did+A_at_mail.gmail.com>



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_at_society.servebeer.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 06 2019 - 21:02:08 CEST

Original text of this message