Oracle Database INSERT phenomenon
This short note is about some phenomenal features of Oracle Database, specifically one of them: the INSERT operation doesn't necessarily involve the table operations described by Julian Dyke in his article Redo Level 11 - Table Operations (DML), which prompted me to write my own CDC solution that directly processes Oracle Database redo logs.
So, a few lines about how data insertions are reflected in the Oracle Database redo log. The expected operations are OP:11.2 IRP (Insert Row Piece) when the inserted data can fit into one data block, or a combination of OP:11.2 IRP and 11.6 ORP (Overflow Row Piece) when row data spans more than one data block. This doesn't just happen in most cases; it happens in 99.999% of cases. However, as I mentioned in the article about SYS.XMLTYPE data sizes in the redo log, sometimes an INSERT statement doesn't generate such changes. These changes are displayed by LogMiner in the V$LOGMNR_CONTENTS.OPERATION column as DIRECT INSERT. Interestingly, the V$LOGMNR_CONTENTS.OPERATION_CODE is 1, just like for a simple INSERT, and it's based on the LGWR operation OP:19.1 (KCBLCOLB - Direct Loader block redo entry). Below is an example of executing ALTER SYSTEM DUMP LOGFILE for a redo record that includes this operation
REDO RECORD - Thread:1 RBA: 0x0002e3.00029911.0074 LEN: 0x2060 VLD: 0x01 CON_UID: 3517775930
SCN: 0x0000000002e1bd6a SUBSCN: 1 04/20/2025 14:12:57
CHANGE #1 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:24.6 ENC:0 FLG:0x0000
CHANGE #2 CON_ID:3 TYP:1 CLS:1 AFN:12 DBA:0x030002bb OBJ:77378 SCN:0x0000000002e1bd6a SEQ:1 OP:19.1 ENC:0 RBL:0 FLG:0x0000
Direct Loader block redo entry
Block header dump: 0x00000003
Object id on Block? Y
seg/obj: 0x12e42 csc: 0x0000000002e1bd66 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x30002b8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.002.00000e03 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x00000003
data_block_dump,data header at 0xffff88534090
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0xffff88534090
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1ee9
avsp=0x1ed5
tosp=0x1ed5
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1ee9
block_row_dump:
tab 0, row 0, @0x1ee9
tl: 151 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 02
col 1: [51]
00 70 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 00 11 ed 18 00 1f 40 90 00
19 22 00 02 1b 68 01 02 01 03 00 03 4b 05 01 03 00 06 e9 07 01 03 00 08 37
06
col 2: [44]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 11 ed 19 00 18 40 90 00
12 21 00 a2 5f 01 01 01 03 00 03 63 05 01 03 00 06 f1 01
col 3: [47]
00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 11 ed 1a 00 1b 40 92 00
05 00 0e 21 81 05 23 35 31 00 a2 5f 01 00 01 03 00 03 7b 02 a2 5f
end_of_block_dump
Dump of memory from 0x0000FFFF88536010 to 0x0000FFFF88536011
FFFF88536010 06C80006 [....]
As always, I'm providing a link to an example of parsing OP:19.1 (KCBLCOLB - Direct Loader block redo entry), and additionally, for operation OP:24.6 (KRVDLR10 - Direct load redo 10g), which often appears with OP:19.1 and is necessary to correctly identify the transaction in which these changes occurred.
Feel free to reach out if you're curious about anything regarding Oracle internals!