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