Oracle 10g可以使用LOGMNR在线分析和挖掘日志，使用当前在线的数据字典，非常方便。
查看包是否已经安装
SQL>desc dbms_logmnr
首先执行一些DDL或DML操作：
SQL> connect eygle/eygle
Connected.
SQL> alter system switch logfile;
System altered.
SQL> create table eygle as select * from dba_users;
Table created.
SQL> set autotrace on
SQL> select count(*) from eygle;
COUNT(*)
----------
19
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 19 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
5 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
然后可以执行LOGMNR解析工作：
SQL> connect / as sysdba
Connected.
SQL> select * from v$log where status='CURRENT';
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
2 1 100 52428800 1 NO CURRENT 12729697 01-JUL-09
SQL> SELECT MEMBER from v$logfile where group#=2;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/mmstest/redo02.log
SQL> exec dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); # 这里可以指定参数：STARTTIME与ENDTIME
PL/SQL procedure successfully completed.
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
136
SQL> select sql_redo from v$logmnr_contents; #同样这里可以执行命令 select sql_undo from v$logmnr_contents;
SQL_REDO
------------------------------------------------------------------------------------------------------------------------
set transaction read write;
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
","REMOTEOWNER","**NAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('25847','25847'
,'31','EYGLE','1',NULL,'2',TO_DATE('01-JUL-09', 'DD-MON-RR'),TO_DATE('01-JUL-09', 'DD-MON-RR'),TO_DATE('01-JUL-09', 'DD-
MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NULL,NULL,NULL,NULL);
set transaction read write;
update "SYS"."CON$" set "CON#" = '10823' where "CON#" = '10822' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
SQL_REDO
------------------------------------------------------------------------------------------------------------------------
update "SYS"."CON$" set "CON#" = '10824' where "CON#" = '10823' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10825' where "CON#" = '10824' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10826' where "CON#" = '10825' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10827' where "CON#" = '10826' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10828' where "CON#" = '10827' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
set transaction read write;
update "SYS"."CON$" set "CON#" = '10829' where "CON#" = '10828' and ROWID = 'AAAAAcAABAAAACqAAM';
commit;
create table eygle as select * from dba_users;
set transaction read write;
Unsupported
update "SYS"."TSQ$" set "TS#" = '0', "GRANTOR#" = '43080', "BLOCKS" = '0', "MAXBLOCKS" = '0', "PRIV1" = '0', "PRIV2" = '
0' where "TS#" = '0' and "GRANTOR#" = '43072' and "BLOCKS" = '0' and "MAXBLOCKS" = '0' and "PRIV1" = '0' and "PRIV2" = '
0' and ROWID = 'AAAAAKAABAAAABbAAF';
commit;
set transaction read write;
SQL> exec dbms_logmnr.end_logmnr