You access the redo data of interest by querying the V$LOGMNR_CONTENTS view. (Note that you must have either the SYSDBA or LOGMINING privilege to query V$LOGMNR_CONTENTS.) This 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, then 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 data types and for rolled back operations.
LogMiner supports Transparent Data Encryption (TDE) in that V$LOGMNR_CONTENTS shows DML operations performed on tables with encrypted columns (including the encrypted columns being updated), provided the LogMiner data dictionary contains the metadata for the object in question and provided the appropriate master key is in the Oracle wallet. The wallet must be open or V$LOGMNR_CONTENTS cannot interpret the associated redo records. TDE support is not available if the database is not open (either read-only or read-write). See Oracle Database Advanced Security Guide for more information about TDE.
Example of Querying V$LOGMNR_CONTENTS
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.
OPERATION SQL_REDO SQL_UNDO
DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
where "ORDER_ID" = '2413' ("ORDER_ID","ORDER_MODE",
and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
and "ORDER_STATUS" = '5' "PROMOTION_ID")
and "ORDER_TOTAL" = '48552' values ('2413','direct','101',
and "SALES_REP_ID" = '161' '5','48552','161',NULL);
and "PROMOTION_ID" IS NULL
and ROWID = 'AAAHTCAABAAAZAPAAN';
DELETE delete from "OE"."ORDERS" insert into "OE"."ORDERS"
where "ORDER_ID" = '2430' ("ORDER_ID","ORDER_MODE",
and "ORDER_MODE" = 'direct' "CUSTOMER_ID","ORDER_STATUS",
and "CUSTOMER_ID" = '101' "ORDER_TOTAL","SALES_REP_ID",
and "ORDER_STATUS" = '8' "PROMOTION_ID")
and "ORDER_TOTAL" = '29669.9' values('2430','direct','101',
and "SALES_REP_ID" = '159' '8','29669.9','159',NULL);
and "PROMOTION_ID" IS NULL
and ROWID = 'AAAHTCAABAAAZAPAAe';
This output shows that user Ron deleted two rows from the oe.orders table. The reconstructed SQL statements are equivalent, but not necessarily identical, to the actual statement that Ron issued. The reason for this is that the original WHERE clause is not logged in the redo log files, so LogMiner can only show deleted (or updated or inserted) rows individually.
Therefore, even though a single DELETE statement may have been responsible for the deletion of both rows, the output in V$LOGMNR_CONTENTS does not reflect that. Thus, the actual DELETE statement may have been DELETE FROM OE.ORDERS WHERE CUSTOMER_ID ='101' or it might have been DELETE FROM OE.ORDERS WHERE PROMOTION_ID = NULL.