Oracle's V$LOGMNR_CONTENTS for a table with a UDT - connecting UPDATE and INTERNAL operations

Question

I'm updating an oracle table with a UDT (user defined type), and then querying the V$LOGMNR_CONTENTS view. I'm seeing that for each row updated there are 2 records - UPDATE and INTERNAL. I need to figure out how to link between them as the UPDATE operation has a temporary value in the ROW_ID and the correct value appears only in the INTERNAL operation, and I'm not sure how do their SCN numbers relate. The way I'm thinking about is to make a queue of UPDATEs per DATA_OBJ#, and link them to the INTERNALs FIFO. Is there something nicer I'm missing?

Script:

CREATE TYPE srulon AS OBJECT (name VARCHAR2(30),phone VARCHAR2(20) );
create table root.udt_table (myrowid rowid, myudt srulon);
BEGIN rdsadmin.rdsadmin_util.switch_logfile;END;
insert into root.udt_table values (null, srulon('small', '1234'));
commit;
BEGIN rdsadmin.rdsadmin_util.switch_logfile;END;
insert into root.udt_table values (null, srulon('small', '1234'));
update root.udt_table set myrowid=rowid, myudt = srulon('smaller', rowid);
commit;
BEGIN rdsadmin.rdsadmin_util.switch_logfile;END;

Query (after START_LOGMNR for the last log):

select scn, SEQUENCE#,operation, SQL_REDO, ROW_ID from  V$LOGMNR_CONTENTS
where session# = 6366 and not operation like '%XML%'
order by scn, SEQUENCE#;

Results:

| SCN | SEQUENCE# | OPERATION | ROW\_ID | SQL\_REDO |
| :--- | :--- | :--- | :--- | :--- |
| 240676056 | 1 | INTERNAL | AAB1avAAAAAAwT7AAA | NULL |
| 240676056 | 1 | UPDATE | AAAAAAAAAAAAAAAAAA | update "ROOT"."UDT\_TABLE" a set a."MYROWID" = 'AAB1avAAAAAAwT7AAA' where a."MYROWID" IS NULL; |
| 240676057 | 5 | INTERNAL | AAB1avAAAAAAwT7AAA | NULL |
| 240676058 | 1 | UPDATE | AAAAAAAAAAAAAAAAAA | update "ROOT"."UDT\_TABLE" a set a."MYROWID" = 'AAB1avAAAAAAwT7AAB' where a."MYROWID" IS NULL; |
| 240676059 | 5 | INTERNAL | AAB1avAAAAAAwT7AAB | NULL |
| 240676069 | 1 | COMMIT | AAAAAAAAAAAAAAAAAA | commit; |

Answer 1

System Change Number (SCN) is the main controlling function that is used to keep track of database transactional activity. SCN is a stamp that defines a committed version of a database at a particular point in time. Every committed transaction gets a unique SCN assigned. DB keeps records of all database changes with the help of SCN numbers. SCN is a running number for the database changes

To get current SCN use

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

So there is no other Connection between the UPDATE and INTERNAL Operation then the Fact that UPDATE SCN is lower then INTERNAL SCN - but no calculated or logical connection

Answer 2

the mistake was to order by scn, SEQUENCE#. once you remove the order by clause, each INTERNAL statement follows its corresponding UPDATE. credit goes to srulon.