Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2839091
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2009-12-02 21:53:18

  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
阅读(1336) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~