Oracle个人技巧 -- LogMiner

col name for a30
col first_change for a10
col next_change for a10

select max(first_time) from v$archived_log
where first_time < to_date('200909251900','yyyymmddhh24mi');

select sequence#,first_time,name,to_char(first_change#,'xxxxxxxx') first_change,
to_char(next_change#,'xxxxxxxx') next_change from v$archived_log
where first_time >=to_date('201011250107','yyyymmddhh24mi') and rownum < 10
order by 2;

execute dbms_logmnr.add_logfile(logfilename=>‘+DATADG/yjsdb/2_426_731963108.dbf’,options=>dbms_logmnr.addfile)
SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'+DATADG/xgdb/1_20334_731968476.dbf') ;

PL/SQL procedure successfully completed.

SQL>
exec dbms_logmnr.start_logmnr();

SQL> spool inssql.txt
SQL> select session_info ,sql_redo from v$logmnr_contents where upper(operation) ='INSERT';

select owner, object_name, object_type, temporary as TEMP from dba_objects where object_id=1285092
SQL> exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>

SQL> select * from dict t where t.table_name like '%LOGMNR%';-看所有与logmnr相关的视图
TABLE_NAME COMMENTS

GV$LOGMNR_CALLBACK Synonym for GV_$LOGMNR_CALLBACK
GV$LOGMNR_CONTENTS Synonym for GV_$LOGMNR_CONTENTS
GV$LOGMNR_DICTIONARY Synonym for GV_$LOGMNR_DICTIONARY
GV$LOGMNR_LOGFILE Synonym for GV_$LOGMNR_LOGFILE
GV$LOGMNR_LOGS Synonym for GV_$LOGMNR_LOGS
GV$LOGMNR_PARAMETERS Synonym for GV_$LOGMNR_PARAMETERS
GV$LOGMNR_PROCESS Synonym for GV_$LOGMNR_PROCESS
GV$LOGMNR_REGION Synonym for GV_$LOGMNR_REGION
GV$LOGMNR_SESSION Synonym for GV_$LOGMNR_SESSION
GV$LOGMNR_STATS Synonym for GV_$LOGMNR_STATS
GV$LOGMNR_TRANSACTION Synonym for GV_$LOGMNR_TRANSACTION
V$LOGMNR_CALLBACK Synonym for V_$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS Synonym for V_$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY Synonym for V_$LOGMNR_DICTIONARY
V$LOGMNR_LOGFILE Synonym for V_$LOGMNR_LOGFILE
V$LOGMNR_LOGS Synonym for V_$LOGMNR_LOGS
V$LOGMNR_PARAMETERS Synonym for V_$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS Synonym for V_$LOGMNR_PROCESS
V$LOGMNR_REGION Synonym for V_$LOGMNR_REGION
V$LOGMNR_SESSION Synonym for V_$LOGMNR_SESSION
TABLE_NAME COMMENTS

V$LOGMNR_STATS Synonym for V_$LOGMNR_STATS
V$LOGMNR_TRANSACTION Synonym for V_$LOGMNR_TRANSACTION