Help - Search - Members - Calendar
Full Version: Insert transaction generates two transaction in logminer
Oracle DBA Forums > Oracle > Oracle Forum
davidmgray

Hi all,

Oracle 9i

Can anyone explain why the following would generate two identical records when mined by logminer?

SQL> select count(*) from centers where center like 'DG%';

COUNT(*)
----------
0

SQL> INSERT INTO "SONICA"."CENTERS" ("CENTER" ,"WORK_MODE" ,"DIRECTED"
2 ,"CYCLE_QTY" ,"CYCLE_FREQ" ,"DESCR" ,"PICK_METHOD" ,
3 "PICK_ZONING" ,"PICK_DEV" ,"LABEL_NO" ,"PRINT_TIME" ,
4 "SUPER_CNT" ,"GROUP_NAME" ,"STG_LOC" ,"SYS" ,"WHO_CREATE" ,
5 "CREATE_DT" ,"UPDATE_DT" ,"WHO_UPDATE" )
6 VALUES ('DG1' ,'M' ,'Y' , NULL , NULL ,'Test' ,'' ,'' ,'' ,
7 '' ,'' , NULL ,'' ,'' ,'N' ,'Me' ,TO_DATE('08-Mar-2010
8 04:05:19 PM', 'dd-Mon-yyyy HH:MI:SS AM') ,
9 TO_DATE('08-Mar-2010 04:05:19 PM', 'dd-Mon-yyyy HH:MI:SS AM')
10 ,'Me' );

1 row created.

SQL> commit;
Commit complete.

SQL> select count(*) from centers where center like 'DG%';

COUNT(*)
----------
1


begin DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 'TURTEST_T1_S1197.ARC', Options =>dbms_logmnr.NEW); end;
begin DBMS_LOGMNR.START_LOGMNR(DictFileName =>'TURTEST_DICTIONARY.ORA', Options => DBMS_LOGMNR.COMMITTED_DATA_ONLY); end;
insert into lm_contents (select * from v$logmnr_contents where operation in ('INSERT','UPDATE','DELETE') );
begin DBMS_LOGMNR.END_LOGMNR; end;

Now if I look in the table lm_contents there are two identical rows which relate to the same INSERT transaction.

Specifically I need the date from sql_redo & sql_undo, both are identical.

Anyone got any thoughts on this?

Thanks
Dave
burleson
Hi Dave,

>> Now if I look in the table lm_contents there are two identical rows which relate to the same INSERT transaction.

Is it the exact same row values?

That would not make sense, because if you replayed the redo log, two rows would be inserted . . .

If it's valid, it may have to do with:

- The internal page cloning algorithm, part of the read consistency mechanism?

- A fragmented insert that chained onto multiple data blocks?

- Oracle 11g compression does versioning:

http://www.dba-oracle.com/oracle11g/sf_Ora...or_the_DBA.html

I'm just guessing . . .
davidmgray
Hi,
The two rows are identical. However if I query the view v$logmnr_contents then all I get is a single row.
So the culplrit must be the INSERT statement although I can't see anything wrong with it...

insert into lm_contents (select * from v$logmnr_contents where operation in ('INSERT','UPDATE','DELETE') );

I only needed this data in a static table for test so as long as the data in the view is ok then I'm happy.

Thanks
Dave



QUOTE (burleson @ Mar 8 2010, 05:27 PM) *
Hi Dave,

>> Now if I look in the table lm_contents there are two identical rows which relate to the same INSERT transaction.

Is it the exact same row values?

That would not make sense, because if you replayed the redo log, two rows would be inserted . . .

If it's valid, it may have to do with:

- The internal page cloning algorithm, part of the read consistency mechanism?

- A fragmented insert that chained onto multiple data blocks?

- Oracle 11g compression does versioning:

http://www.dba-oracle.com/oracle11g/sf_Ora...or_the_DBA.html

I'm just guessing . . .

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.