This example shows how to see all changes made to the database in a specific time range by a single user:
joedevo. Connect to the database and then take the following steps:
Create the LogMiner dictionary file.
To use LogMiner to analyze
joedevo's data, you must either create a LogMiner dictionary file before any table definition changes are made to tables that
joedevo uses or use the online catalog at LogMiner startup. See "Extract a LogMiner Dictionary" for examples of creating LogMiner dictionaries. This example uses a LogMiner dictionary that has been extracted to the redo log files.
Add redo log files.
joedevo has made some changes to the database. You can now specify the names of the redo log files that you want to analyze, as follows:
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => 'log1orc1.ora', - OPTIONS => DBMS_LOGMNR.NEW);
If desired, add additional redo log files, as follows:
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => 'log2orc1.ora', - OPTIONS => DBMS_LOGMNR.ADDFILE);
Start LogMiner and limit the search to the specified time range:
EXECUTE DBMS_LOGMNR.START_LOGMNR( - DICTFILENAME => 'orcldict.ora', - STARTTIME => TO_DATE('01-Jan-1998 08:30:00','DD-MON-YYYY HH:MI:SS'), - ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
At this point, the
V$LOGMNR_CONTENTS view is available for queries. You decide to find all of the changes made by user
joedevo to the
salary table. Execute the following
SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE USERNAME = 'joedevo' AND SEG_NAME = 'salary';
For both the
SQL_UNDO columns, two rows are returned (the format of the data display will be different on your screen). You discover that
joedevo requested two operations: he deleted his old salary and then inserted a new, higher salary. You now have the data necessary to undo this operation.
SQL_REDO SQL_UNDO -------- -------- delete from SALARY insert into SALARY(NAME, EMPNO, SAL) where EMPNO = 12345 values ('JOEDEVO', 12345, 500) and NAME='JOEDEVO' and SAL=500; insert into SALARY(NAME, EMPNO, SAL) delete from SALARY values('JOEDEVO',12345, 2500) where EMPNO = 12345 and NAME = 'JOEDEVO' 2 rows selected and SAL = 2500;
End the LogMiner session.
DBMS_LOGMNR.END_LOGMNR procedure to finish the LogMiner session properly: