logmnr无法生成部分表的DML跟踪语句的解决方法：
新建映射数据字典信息文件：
SQL> execute dbms_logmnr_d.build('shwdict.ora','d:oracleoradatarmanutl');
PL/SQL procedure successfully completed
SQL> insert into test values (1,'sdf
2 ');
SQL> exec dbms_logmnr.add_logfile('d:oracleoradatarmanarchive1_332.DBF',dbms_logmnr.addfile);
PL/SQL procedure successfully completed
SQL> select * from v$log_history;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- ----------- ------------
334 593625290 1 334 6624931 2006-6-20 1 6625329
335 593625882 1 335 6625329 2006-6-20 1 6626813
336 593626064 1 336 6626813 2006-6-20 1 6627301
SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:oracleoradatarmanutlshwdict.ora',startScn => '6624931',endScn => '6627301');
PL/SQL procedure successfully completed
SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO
SQL> FROM V$LOGMNR_CONTENTS
SQL> WHERE SEG_OWNER = 'SILENCE' and OPERATION='INSERT';
OPERATION SQL_REDO SQL_UNDO
-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
INSERT insert into "SILENCE"."TEST"("ID","TEXT") values ('1','sdf delete from "SILENCE"."TEST" where "ID" = '1' and "TEXT" = 'sdf
'); ' and ROWID = 'AAAHdAAABAAAMdqAAC';
新建表AA 并对AA 进行添加删除修改等DML语句操作:
SQL> create table aa as select * from dba_objects;
Table created
SQL> alter table aa add constraint pk_object_id primary key (object_id);
Table altered
SQL> delete from aa where rownum<=1000;
1000 rows deleted
SQL> commit;
Commit complete
SQL> update aa set owner='silence' where object_id=22727;
1 row updated
SQL> commit;
Commit complete
SQL> insert into aa (object_id) values (1101010101);
1 row inserted
SQL> commit;
Commit complete
对test 、test_1 进行添加删除修改等DML语句操作
SQL> DESC test_1
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER Y
TEXT VARCHAR2(100) Y
SQL> desc test
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER Y
TEXT VARCHAR2(100) Y
SQL> insert into test_1 values (1,'dsf');
1 row inserted
SQL> commit;
Commit complete
SQL> insert into test values (1,'dsf');
1 row inserted
SQL> commit;
SQL> update test set id=2 where text='test';
1 row updated
SQL> delete from test_1 where id=1;
1 row deleted
SQL> commit;
SQL> exec dbms_logmnr.add_logfile('d:oracleoradatarmanarchive1_333.DBF',dbms_logmnr.addfile);
PL/SQL procedure successfully completed
SQL> select * from v$log_history;
RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- ----------- ------------
334 593625290 1 334 6624931 2006-6-20 1 6625329
335 593625882 1 335 6625329 2006-6-20 1 6626813
336 593626064 1 336 6626813 2006-6-20 1 6627301
337 593626171 1 337 6627301 2006-6-20 1 6627621
338 593627862 1 338 6627621 2006-6-20 1 6631912
SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:oracleoradatarmanutlshwdict.ora',startScn => '6579577',endScn => '6624357');
PL/SQL procedure successfully completed
SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO
SQL> FROM V$LOGMNR_CONTENTS
SQL> WHERE SEG_OWNER = 'SILENCE' and OPERATION='INSERT';
OPERATION SQL_REDO SQL_UNDO
-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
DDL create table aa as select * from dba_objects
;
DDL CREATE UNIQUE INDEX "SILENCE"."PK_OBJECT_ID" on "SILENCE"."AA"("OBJECT_ID") NOPA
DDL alter table aa add constraint pk_object_id primary key (object_id)
;
INSERT insert into "SILENCE"."TEST_1"("ID","TEXT") values ('1','dsf'); delete from "SILENCE"."TEST_1" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = '
INSERT insert into "SILENCE"."TEST"("ID","TEXT") values ('1','dsf'); delete from "SILENCE"."TEST" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = 'AA
UPDATE update "SILENCE"."TEST" set "ID" = '2' where "ID" = '1' and ROWID = 'AAAHdAAABAA update "SILENCE"."TEST" set "ID" = '1' where "ID" = '2' and ROWID = 'AAAHdAAABAA
DDL create table test_2 as select * from test_1
;
DDL drop table test_2
;
DELETE delete from "SILENCE"."TEST_1" where "ID" = '1' and "TEXT" = 'dsf' and ROWID = ' insert into "SILENCE"."TEST_1"("ID","TEXT") values ('1','dsf');
重新生成数据字典映射文件:
SQL> execute dbms_logmnr_d.build('shwdict.ora','d:oracleoradatarmanutl');
PL/SQL procedure successfully completed
SQL> update aa set owner='silence' where OBJECT_ID=13314;
1 row updated
SQL> commit;
SQL> delete from aa where OBJECT_ID=13314;
1 row deleted
SQL> commit;
Commit complete
SQL> alter system checkpoint;
System altered
SQL> alter system switch logfile;
System altered
SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO
SQL> FROM V$LOGMNR_CONTENTS
SQL> WHERE SEG_OWNER = 'SILENCE' ;
OPERATION SQL_REDO SQL_UNDO
-------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'DEFROLE$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROFILE$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'PROFNAME$' insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'DEPENDENCY insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'ACCESS$' a insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'AUD$' and insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'LINK$' and insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
DELETE delete from "SILENCE"."AA" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'TRUSTED_LI insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
INTERNAL
UPDATE update "SILENCE"."AA" set "OWNER" = 'silence' where "OWNER" = 'SYS' and ROWID = update "SILENCE"."AA" set "OWNER" = 'SYS' where "OWNER" = 'silence' and ROWID =
DELETE delete from "SILENCE"."AA" where "OWNER" = 'silence' and "OBJECT_NAME" = '/6a00f insert into "SILENCE"."AA"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
INTERNAL
由于aa 表是在映射数据字典信息文件生成后新建的。
因此，在生成跟踪视图(v$logmnr_content) 只有aa 表的DDL语句，却没有任何关于aa表的dml语句信息。
因此在做logmnr 做本地或远程数据库跟踪前最好重新生成数据字典映射文件。
备注：
配置LOGMNR:
execute dbms_logmnr_d.build('shwdict.ora','c:oracle');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
指定utl_file_dir 参数：
我们要在操作logminer的数据库服务器上配置该参数。以让logminer能找到我们上一步产生的数据字典文件。
utl_file_dir = 'c:oracle'
添加要分析的日志：
在配置完以上两个步骤后，我们就可以进行日志分析了。
对于第一个需要分析的日志我们可以进行以下命令：
execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00126.001',dbms_logmnr.new);
如果不是第一个日志文件，
我们可以：execute dbms_logmnr.add_logfile('C:oracleora92rdbmsARC00130.001',dbms_logmnr.addfile);
设置时间格式：
必须设置如下时间格式，否则，在进行日志分析的过程中，
将报错误信息：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
分析日志： 我们可以发出以下命令，来进行日志分析了：
dbms_logmnr.start_logmnr(
dictfilename => 'C:oracleshwdict.ora', startscn=>xxxxxx,endscn=>xxxxx,
starttime => to_date('20030501 12:15:00','yyyymmdd hh24:mi:ss'),
endtime => to_date('20030501 15:40:30','yyyymmdd hh24:mi:ss'));
注意：大家必须知道dictfilename数据字典文件；scn的大小可以通过v$log_history来查询，
endscn则最好比查询得到的最后一个scn小一个数字。
查询结果： 读者可以查询v$logmnr_contents来获得日志分析结果。
注意事项： 进行logminer的db的OS平台、DB的字符集必须跟原来db的一致。
