LogMiner session

Tip # 2: Starting, using, and ending a LogMiner session
We are now ready to mine for gold, well, SQL. Suppose Scott calls you and says he deleted rows from his emp table (where empno is greater than 7900). It was a mistake, and he needs the data restored to his table. The first step is to start LogMiner and populate v$logmnr_contents. This view or "table" is what you query against to extract the SQL_REDO and SQL_UNDO statements.
There are several options as to how you gather the contents. More than likely, you're going to know a time range as opposed to an SCN number, so knowing approximately when Scott deleted the rows is all we need from him (aside from the table name).

SQL> exec dbms_logmnr.start_logmnr( -
> dictfilename =>
'c:\ora9i\admin\db00\file_dir\
dictionary.ora', -
> starttime =>
to_date('06-Jun-2004 17:30:00',
'DD-MON-YYYY HH24:MI:SS'), -
> endtime =>
to_date('06-Jun-2004 17:35:00',
'DD-MON-YYYY HH24:MI:SS'));

PL/SQL procedure successfully completed.

Now we are ready to see what took place.
SQL> select sql_redo, sql_undo
2 from v$logmnr_contents
3 where username = 'SCOTT'
4 and seg_name = 'EMP';

SQL_REDO
------------------------------------------------------------------------------------------
SQL_UNDO
------------------------------------------------------------------------------------------
delete from "SCOTT"."EMP" where "EMPNO" = '7902' and "ENAME" = 'FORD' and "JOB" = 'ANALYST
' and "MGR" = '7566' and "HIREDATE" = TO_DATE('03-DEC-81', 'DD-MON-RR') and "SAL" = '3000'
and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAHW7AABAAAMUiAAM';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7902','FORD','ANALYST','7566',TO_DATE('03-DEC-81', 'DD-MON-RR'),'3000',NULL,'20');

*******************************************************************************************
delete from "SCOTT"."EMP" where "EMPNO" = '7934' and "ENAME" = 'MILLER' and "JOB" = 'CLERK
' and "MGR" = '7782' and "HIREDATE" = TO_DATE('23-JAN-82', 'DD-MON-RR') and "SAL" = '1300'
and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAHW7AABAAAMUiAAN';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7934','MILLER','CLERK','7782',TO_DATE('23-JAN-82', 'DD-MON-RR'),'1300',NULL,'10');

********************************************************************************************
delete from "SCOTT"."EMP" where "EMPNO" = '7935' and "ENAME" = 'COLE' and "JOB" = 'LINDA'
and "MGR" = '7839' and "HIREDATE" = TO_DATE('01-MAY-04', 'DD-MON-RR') and "SAL" = '4000'
and "COMM" IS NULL and "DEPTNO" = '30' and ROWID = 'AAAHW7AABAAAMUiAAO';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7935','COLE','LINDA','7839',TO_DATE('01-MAY-04', 'DD-MON-RR'),'4000',NULL,'30');

You can see how Oracle (shading and "****" lines were added for readability) took the "delete from emp where empno > 7900" statement and turned it into something more complex. It should be apparent that the SQL_UNDO statements are practically in a cut and paste state, ready for immediate use.
To end your LogMiner session, issue the following command:
SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.