Skip to content
January 13, 2017 / doganay

LOG MINER SCRIPT CREATOR


undefine V_START V_END
set pages 1000 lines 1000 feed off
accept V_START prompt "START TIME (dd.mm.yyyy hh24:mi:ss): "
accept V_END     prompt "END TIME (dd.mm.yyyy hh24:mi:ss): "

SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || name
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$archived_log
 WHERE     first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') UNION ALL SELECT    'exec dbms_logmnr.add_logfile(LogFileName => '''
       || MIN (MEMBER)
       || ''',Options => dbms_logmnr.ADDFILE);'
  FROM gv$log a, gv$logfile b
 WHERE     a.inst_id = b.inst_id
       AND a.group# = b.group#
       AND a.first_time >= TO_DATE ('&&V_START', 'dd.mm.yyyy hh24:mi:ss')
       AND a.first_time <= TO_DATE ('&&V_END', 'dd.mm.yyyy hh24:mi:ss') GROUP BY a.group# UNION ALL SELECT 'exec dbms_logmnr.start_logmnr(options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);'
  FROM DUAL
UNION ALL
SELECT '--select * from gv$logmnr_contents;' FROM DUAL
UNION ALL
SELECT '--exec DBMS_LOGMNR.END_LOGMNR;' FROM DUAL;

prompt
set feed on
undefine V_START V_END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: