LogMiner lets you make queries based on column values. For instance, you can perform a query to show all updates to the
hr.employees table that increase
salary more than a certain amount. Data such as this can be used to analyze system behavior and to perform auditing tasks.
LogMiner data extraction from redo log files is performed using two mine functions:
DBMS_LOGMNR.COLUMN_PRESENT. Support for these mine functions is provided by the
UNDO_VALUE columns in the
The following is an example of how you could use the
MINE_VALUE function to select all updates to
hr.employees that increased the
salary column to more than twice its original value:
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_NAME = 'EMPLOYEES' AND SEG_OWNER = 'HR' AND OPERATION = 'UPDATE' AND DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') > 2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY');
As shown in this example, the
MINE_VALUE function takes two arguments:
The first one specifies whether to mine the redo (
REDO_VALUE) or undo (
UNDO_VALUE) portion of the data. The redo portion of the data is the data that is in the column after an insert, update, or delete operation; the undo portion of the data is the data that was in the column before an insert, update, or delete operation. It may help to think of the
REDO_VALUE as the new value and the
UNDO_VALUE as the old value.
The second argument is a string that specifies the fully qualified name of the column to be mined (in this case,
MINE_VALUE function always returns a string that can be converted back to the original data type.
The specified column is not present in the redo or undo portion of the data.
The specified column is present and has a null value.
To distinguish between these two cases, use the
COLUMN_PRESENT function which returns a
1 if the column is present in the redo or undo portion of the data. Otherwise, it returns a
0. For example, suppose you wanted to find out the increment by which the values in the
salary column were modified and the corresponding transaction identifier. You could issue the following SQL query:
SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, (DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') - DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY')) AS INCR_SAL FROM V$LOGMNR_CONTENTS WHERE OPERATION = 'UPDATE' AND DBMS_LOGMNR.COLUMN_PRESENT(REDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1 AND DBMS_LOGMNR.COLUMN_PRESENT(UNDO_VALUE, 'HR.EMPLOYEES.SALARY') = 1;
The following usage rules apply to the
They can only be used within a LogMiner session.
They must be started in the context of a select operation from the
They do not support
COLLECTION data types.
DBMS_LOGMNR.MINE_VALUE function is used to get an
NCHAR value that includes characters not found in the database character set, then those characters are returned as the replacement character (for example, an inverted question mark) of the database character set.