Update trigger in oracle on updating a column
CHANGE #4 TYP:0 CLS:18 AFN:3 DBA:0x00c00694 OBJ:4294967295 SCN:0x0000.0032b4a6 SEQ:1 OP:5.1 ENC:0 RBL:0 ...
Undo type: Regular undo Begin trans Last buffer split: No ...
Direct mode inserts aka nologging inserts are not discussed in this blog entry.
Changes made by the SQL statement might be rolled back too.
This was the essence of a question asked in Oracle-l list and I think, it is a good topic for further discussion.
Jared Still came up with a fine method to understand this issue measuring redo/undo size.
To keep the redo log file dumps as clean as possible, we need a database with no activity.
(or) Does RDBMS engine have an optimization skipping the update, as value of that column is not changing?Then, We update the row with the same value from another session and commit.From Session #1, we switch log again from session #1.KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 0 op: L itl: xid: 0x000a.00d.000005ae uba: 0x00c00996.01a7.2c flg: C--- lkc: 0 scn: 0x0000.0032b3f1 KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100113d hdba: 0x0100113a itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 1 nnew: 1 size: 0 Vector content: col 0: [ 5] 52 69 79 61 6a In a nutshell, update to the table row with the same value, updated the row value from ‘Riyaj’ to ‘Riyaj’.