Sorting V$LOGMNR_CONTENTS to recreate long SQL statements.
Date: Thu, 06 Jun 2019 13:42:37 -0500
Message-ID: <7b20b7fbefd1d41b7eecbaad7c572a9c_at_society.servebeer.com>
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-lReceived on Thu Jun 06 2019 - 20:42:37 CEST