SQL Script

Logminer Script

Updated: 03-Jan-2003
Version: 1.0

Description

Simple Logminer script to analyze one log file

Parameters

dict_location - location of flat file dictionary
mylog - the redo log to analyze
startdt - Logminer Start Date
enddt - Logminer End Date

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set ver on
set serveroutput on size 100000

show parameter utl_file

col member form a60
select a.status, b.member 
from v$logfile b, v$log a
where b.group#=a.group#
;

ACCEPT dict_location PROMPT 'Enter directory for Dictionary location (include /): '
ACCEPT mylog PROMPT 'Enter Log File Name to be mined : '
ACCEPT startdt PROMPT 'Enter Start Date (dd-mon-yyyy hh24:mi) : '
ACCEPT enddt PROMPT 'Enter End Date (dd-mon-yyyy hh24:mi) : '

alter session set nls_date_format='dd-mon-yyyy hh24:mi';

spool logmgr.lst

begin
   dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora'
                      ,dictionary_location => '&dict_location');  

   dbms_logmnr.add_logfile('&mylog',DBMS_LOGMNR.NEW ); 

   dbms_logmnr.start_logmnr (starttime=>'&startdt'
                           , endtime=>'&enddt'
                           , dictfilename=>'&dict_location.dictionary.ora'); 

end;
/


select * from v$logmnr_dictionary;
select * from v$logmnr_parameters;
select * from v$logmnr_logs;

drop table mylogmnr;
create table mylogmnr as select * from v$logmnr_contents;
commit;

begin
   dbms_logmnr.end_logmnr;
end;
/

select timestamp,operation, sql_redo, sql_undo from mylogmnr 
where seg_owner ='&user'
and seg_name = '&segment_name';


spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Logminer Script

Updated: 03-Jan-2003Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 03-Jan-2003
Version: 1.0