Understanding logminer results -- rowid on clob inserts
In using log miner I have noticed that inserts into rows that contain a CLOB (I assume this applies to other LOB type fields as well, have only tested with CLOB so far) field are actually recorded as two DML entries.
--the first entry is the insert operation that inserts all values with an EMPTY_CLOB() for the CLOB field
--the second entry is the update that sets the actual CLOB value
This separation makes sense as there may be separate locations that the values are being stored etc. However, what I am tripping over is the fact the first entry, the Insert, has a RowId value of 'AAAAAAAAAAAAAAAAAA' which is invalid if I attempt to use it in a flashback query such as:
'SELECT * FROM PERSON AS OF SCN #####' where RowId = 'AAAAAAAAAAAAAAAAAA'
The second operation, the Update of the CLOB field, has the valid RowId.
Now, again, this makes sense if the insert of the new row is not really considered "done" until the two steps are done. However, is there some way to group these operations together when analyzing the log contents to know that these two operations are a "matched set"?
Not a total deal breaker, but would be nice to know what is happening under the hood here so I don't act on any false assumptions.
To replicate:
Create a table with CLOB field:
CREATE TABLE DEVUSER.TESTTABLE
(
ID NUMBER
, FULLNAME VARCHAR2(50)
, AGE NUMBER
, DESCRIPTION CLOB
);
Capture the before SCN:
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
Insert a new row in the test table:
INSERT INTO TESTTABLE(ID,FULLNAME,AGE) VALUES(1,'Robert BUILDER',35);
COMMIT;
Capture the after SCN:
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
Start logminer session with the bracketing scn values and options etc:
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN=>2619174, ENDSCN=>2619191, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.NO_ROWID_IN_STMT + DBMS_LOGMNR.NO_SQL_DELIMITER)
Query the logs for the changes in that range:
SELECT
commit_scn, xid,operation,table_name,operation,row_id
,sql_redo,sql_undo, rs_id,ssn
FROM V$LOGMNR_CONTENTS
ORDER BY xid asc,sequence# asc