René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

dbms_logmnr

This page tries to give a recipe on how to use dbms_logmnr.
First, make sure that the database runs in archive log mode and that the archiver (ARCn) is started: 
select value from v$parameter
where name='log_archive_start';
This query should return TRUE. 
select log_mode from v$database;
This query should return ARCHIVELOG
I want to make sure a new archive log is being written to: 
alter system switch logfile;
Finding out which file will be the next archive log: 
select 
  dest.value||replace(form.value, '%s', sequence#)
from
  v$parameter dest,
  v$parameter form,
  v$log
where
  dest.name='log_archive_dest'   and
  form.name='log_archive_format' and
  status  = 'CURRENT';
This statement returns a path and a filename which will be used later.
Executing some SQL statements: 
create table logmnr_test (
  a number,
  b varchar2(10),
  c date
);

insert into logmnr_test values (1, 'one',   sysdate);
insert into logmnr_test values (2,  null,   to_date('28.08.1970','dd.mm.yyyy'));
insert into logmnr_test values (3, 'thrre', sysdate-5);

commit;
Again switching the logfile: 
alter system switch logfile;
This switch causes an archive log file to be created with the filename from the statement above. 
begin
  sys.dbms_logmnr.add_logfile(
    logfilename => '/db/adpdb/backup/1465.arc'
  );
end;
/
   

begin
  sys.dbms_logmnr.start_logmnr (
    starttime => sysdate - 10 * 1/24/60,
    endtime   => sysdate,
    options   => sys.dbms_logmnr.dict_from_online_catalog);
end;
/
start_logmnr fills v$logmnr_contents. 
select substr(sql_redo,1,150) from
v$logmnr_contents;
By default, the data that is made visible through v$logmnr_contents is populated in the system tablespace. If you want to choose another tablespace, specify it with dbms_logmnr_d.set_tablespace.

Procedures

add_logfile

start_logmnr