POUWIEL|COM

JeroenPouwiel

Logminer

To retrieve or correct (mostly user-) data using Logminer:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

BEGIN
   EXECUTE DBMS_LOGMNR.START_LOGMNR(
   STARTTIME => '19-SEP-2008 10:00:00',
   ENDTIME => '19-SEP-2008 08:45:00',
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
   DBMS_LOGMNR.PRINT_PRETTY_SQL +
   DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/

Now you can query the V$LOGMNR_CONTENTS for any statements you might be interested in, like:

SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, 
       SQL_REDO FROM V$LOGMNR_CONTENTS 
       WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND
       TIMESTAMP > '10-jan-2003 15:59:53';

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');

SELECT OPERATION, SQL_REDO, SQL_UNDO
       FROM V$LOGMNR_CONTENTS
       WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
       OPERATION = 'DELETE' AND USERNAME = 'RON';

Source: click

Comments are closed.

Categories