Querying V$LOGMNR_CONTENTS for Redo Data of Interest

Querying V$LOGMNR_CONTENTS for Redo Data of Interest
You access the redo data of interest by querying the V$LOGMNR_CONTENTS view. (Note that you must have the SELECT ANY TRANSACTION privilege to query V$LOGMNR_CONTENTS.) Per email from Diana Lorentz/Doug Voss on 3/16/05This view provides historical information about changes made to the database, including (but not limited to) the following:
• The type of change made to the database: INSERT, UPDATE, DELETE, or DDL (OPERATION column).
• The SCN at which a change was made (SCN column).
• The SCN at which a change was committed (COMMIT_SCN column).
• The transaction to which a change belongs (XIDUSN, XIDSLT, and XIDSQN columns).
• The table and schema name of the modified object (SEG_NAME and SEG_OWNER columns).
• The name of the user who issued the DDL or DML statement to make the change (USERNAME column).
• If the change was due to a SQL DML statement, the reconstructed SQL statements showing SQL DML that is equivalent (but not necessarily identical) to the SQL DML used to generate the redo records (SQL_REDO column).
• If a password is part of the statement in a SQL_REDO column, the password is encrypted. SQL_REDO column values that correspond to DDL statements are always identical to the SQL DDL used to generate the redo records.
• If the change was due to a SQL DML change, the reconstructed SQL statements showing the SQL DML statements needed to undo the change (SQL_UNDO column).
SQL_UNDO columns that correspond to DDL statements are always NULL. The SQL_UNDO column may be NULL also for some datatypes and for rolled back operations.
For example, suppose you wanted to find out about any delete operations that a user named Ron had performed on the oe.orders table. You could issue a SQL query similar to the following:
SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
OPERATION = 'DELETE' AND USERNAME = 'RON';

The following output would be produced. The formatting may be different on your display than that shown here.