logmnr無法生成部分表的DML跟蹤語句問題

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的一致。

 
用SQL語句來建立跟蹤的問題
/*-- 下面是我用SQL的事件探察器設置跟蹤,並生成的SQL腳本 現在有幾個問題: 1.這個跟蹤不會産生數據,用: select * from ::fn_trace_getinfo(1) 可以看到狀態是在運行,但 c:\test.trc 始終是空文件 2.在事件探察器中...查看完整版>>用SQL語句來建立跟蹤的問題
 
直接從SQL語句問題貼子數據建表並生成建表語句的存儲過程
下面的存儲過程,可幫你在回答SQL語句問題時,直接從貼子的樣本數據建表並生成建表語句,省去大量的手工輸入數據的工作。/*Create Table from your web page data* 2004-JAN-1, OpenVMS,V0.1* 2004-JAN-2, V0.5, ad...查看完整版>>直接從SQL語句問題貼子數據建表並生成建表語句的存儲過程
 
查看一個Oracle表的數據定義語句。
   前提:要求使用SQL PLUS。SQL> set pages 0SQL> set long 1000000SQL> select dbms_metadata.get_ddl('TABLE','表名','用戶名') from dual;...查看完整版>>查看一個Oracle表的數據定義語句。
 
Oracle技巧:用v$session_longops跟蹤DDL語句
  Oracle數字字典包含一個鮮爲人知的v$session_longops視圖。v$session_longops視圖可以使Oracle專家減少運行時間很長的DDL和DML語句的運行時間。        ...查看完整版>>Oracle技巧:用v$session_longops跟蹤DDL語句
 
Oracle中如何用T-SQL語句建立跟蹤
一、用腳本啓動並設置跟蹤的示例 通過這個示例,你可以了解用腳本進行跟蹤所涉及到的存儲過程,要了解這些存儲過程的具體語法和參數的含義,請查詢聯機幫助 /****************************************************/...查看完整版>>Oracle中如何用T-SQL語句建立跟蹤