You access the redo data of interest by querying the
V$LOGMNR_CONTENTS view. (Note that you must have either the
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:
The SCN at which a change was made (
The SCN at which a change was committed (
The transaction to which a change belongs (
The table and schema name of the modified object (
The name of the user who issued the DDL or DML statement to make the change (
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 (
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 columns that correspond to DDL statements are always
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.