Scenario 1: Using LogMiner to Track Changes Made by a Specific User

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:

  1. 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.

  2. Add redo log files.

    Assume that 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:

       LOGFILENAME => 'log1orc1.ora', -

    If desired, add additional redo log files, as follows:

       LOGFILENAME => 'log2orc1.ora', -
  3. Start LogMiner and limit the search to the specified time range:

       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'));
  4. Query the V$LOGMNR_CONTENTS view.

    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 statement:

       WHERE USERNAME = 'joedevo' AND SEG_NAME = 'salary';

    For both the SQL_REDO and 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;
  5. End the LogMiner session.

    Use the DBMS_LOGMNR.END_LOGMNR procedure to finish the LogMiner session properly: