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.MINE_VALUE and DBMS_LOGMNR.COLUMN_PRESENT. Support for
these mine functions is provided by the REDO_VALUE and UNDO_VALUE columns in
the V$LOGMNR_CONTENTS view.
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, hr.employees.salary). The MINE_VALUE
function always returns a string that can be converted back to the original
datatype
The Meaning of NULL Values Returned by the MINE_VALUE Function
If the MINE_VALUE function returns a NULL value, it can mean either:
■ 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 DBMS_LOGMNR.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;
Usage Rules for the MINE_VALUE and COLUMN_PRESENT Functions
The following usage rules apply to the MINE_VALUE and COLUMN_PRESENT
functions:
■ They can only be used within a LogMiner session.
■ They must be invoked in the context of a select operation from the V$LOGMNR_
CONTENTS view.
■ They do not support LONG, LONG RAW, CLOB, BLOB, NCLOB, ADT, or
COLLECTION datatypes
阅读(1429) | 评论(0) | 转发(0) |
