insert操作放在undo中的rowid怎麼來的！
SQL> create table t1(id int , name varchar2(10)) tablespace users pctfree 99;
表已建立。
SQL> insert into t1 values(1 , 'a');
已建立 1 行。
SQL> insert into t1 values(2 , 'b');
已建立 1 行。
SQL> insert into t1 values(3 , 'c');
已建立 1 行。
SQL> insert into t1 values(4 , 'd');
已建立 1 行。
SQL> insert into t1 values(5 , 'e');
已建立 1 行。
SQL> insert into t1 values(6 , 'f');
已建立 1 行。
SQL> insert into t1 values(7 , 'g');
已建立 1 行。
SQL> insert into t1 values(8 , 'h');
已建立 1 行。
SQL> insert into t1 values(9 , 'i');
已建立 1 行。
SQL> select rowid, dbms_rowid.rowid_object(rowid) dobj,dbms_rowid.rowid_relative
_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block_number ,dbms_rowid.ro
wid_row_number(rowid) row_number, id , name from t1;
ROWID DOBJ FNO BLOCK_NUMBER ROW_NUMBER ID N
------------------ ---------- ---------- ------------ ---------- ---- -
AAAC5RAAEAAAABMAAA 11857 4 76 0 1 a
AAAC5RAAEAAAABMAAB 11857 4 76 1 2 b
AAAC5RAAEAAAABMAAC 11857 4 76 2 3 c
AAAC5RAAEAAAABMAAD 11857 4 76 3 4 d
AAAC5RAAEAAAABMAAE 11857 4 76 4 5 e
AAAC5RAAEAAAABMAAF 11857 4 76 5 6 f
AAAC5RAAEAAAABMAAG 11857 4 76 6 7 g
AAAC5RAAEAAAABNAAA 11857 4 77 0 8 h
AAAC5RAAEAAAABNAAB 11857 4 77 1 9 i
已選擇9行。
SQL>
--===========================
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,start_ubablk from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK START_UBABLK
---------- ---------- ---------- ---------- ---------- ------------
23 21 350 10 1617 1617
SQL>
--===========================================
trace資訊：
--===========================================
* Rec #0xf slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x02800918.0091.3d ctl max scn: 0x0000.000a0453 prv tx scn: 0x0000.000a046c
txn start scn: scn: 0x0000.000a5e7b logon user: 0
prev brb: 41945366 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
*-----------------------------
* Rec #0x10 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x0f
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.0f
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
*-----------------------------
* Rec #0x11 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x10
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.10
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
*-----------------------------
* Rec #0x12 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.11
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3)
*-----------------------------
* Rec #0x13 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x12
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.12
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 4(0x4)
*-----------------------------
* Rec #0x14 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x13
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.13
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5)
*-----------------------------
* Rec #0x15 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x14
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.14
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6)
*-----------------------------
* Rec #0x16 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x15
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004d hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
*-----------------------------
* Rec #0x17 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x16
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x02800651.0092.16
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004d hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
End dump data blocks tsn: 9 file#: 10 minblk 1617 maxblk 1617
--=============================
undo block被dump之後我們清楚的發現：
insert into t1表的每一條資料在undo中都對應著一個Rec，具體rec中記錄的最有用的資訊是:
1.objn: 11857(0x00002e51) --data_object_id
2.bdba: 0x0100004d --relative_fno block_number
3.slot: 1(0x1) --row_number
以最後插入的insert into t1 values(9 , 'i')為例：
先把2中的bdba分解一下：
SQL> select dbms_utility.data_block_address_file(to_number('0100004d','xxxxxxxx'
)) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('0100004D','XXXXXXXX'))
----------------------------------------------------------------------
4
SQL> select dbms_utility.data_block_address_block(to_number('0100004d','xxxxxxxx
')) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('0100004D','XXXXXXXX'))
-----------------------------------------------------------------------
77
SQL>
objn和上面分解出來的relative_fno、block_number以及row_number(slot)
合起來不正是其對應的rowid嘛
如果看起來還不直觀的話再成生rowid看看：
SQL> select dbms_rowid.rowid_create(1,11857,4,77,1) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAC5RAAEAAAABNAAB
SQL>
這就是很多書上以及很多人們常說的insert操作會把rowid放在undo的原因吧，其實
不是直接放的rowid，也是拼湊出來的，這樣也符合rowid的含義：偽列
如果rollback的話，那麼根據儲存在undo中的rowid直接刪除插入的行。
insert使用undo不太容易理解，因為rowid需要拼湊，而update和delete非常容易理解，可以直接在undo中很容易的找到其old image，這裡就不贅述了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1010520/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 不同insert操作產生的undo的測試
- 鍵盤insert操作怎麼取消？電腦鍵盤insert操作取消教程
- MySQL中的_rowidMySql
- Oracle中的insert/insert all/insert firstOracle
- 淺談Rowid中的行號
- win10系統我的電腦怎麼放在桌面_win10怎麼把此電腦放在桌面Win10
- 存放在電腦中的檔案丟失了怎麼恢復
- go中，iota要放在const的最前面來宣告Go
- 怎樣提高insert的效能
- 關於oracle中的undoOracle
- 【ROWID】Oracle rowid說明Oracle
- python中的字典賦值操作怎麼實現？Python賦值
- Mac OS的牆紙放在哪個資料夾？怎麼修改？Mac
- 技術分享 | undo 太大了怎麼辦
- 解析MySQL中INSERT INTO SELECT的使用MySql
- MySQL中的redo log和undo logMySql
- 怎麼來開啟Mac中的Root賬戶？Mac
- appium 怎麼禁止跳出 app 的操作APP
- Sqlserver沒有單獨的undo檔案，使用tempdb和redo log來存放undo資料SQLServer
- GreatSQL 中 Insert 慢是什麼情況？SQL
- 想在Safari中禁用自動播放影片要怎麼操作？教程來了，快來看看吧！
- [NGX]使用ViewContainerRef來操作Angular中的DOMViewAIAngular
- win10如何把我的電腦圖示放在桌面 win10桌面我的電腦怎麼調出來Win10
- win10怎麼把軟體放在桌面 win10如何把app圖示放在桌面Win10APP
- MySQL中REPLACE INTO和INSERT INTO的區別分析MySql
- NSCache快取怎麼來的快取
- insert all和insert first語句的用法
- 淺析MySQL事務中的redo與undoMySql
- Seata-AT模式+TDDL:構建Insert操作的後映象在執行SELECT LAST_INSERT_ID()時報錯模式AST
- Oracle redo解析之-4、rowid的計算Oracle Redo
- Sqlserver、oracle中Merge的使用方法,一個merge語句搞定多個Insert,Update,Delete操作SQLServerOracledelete
- Innodb undo之 undo物理結構的初始化
- update誤操作後 通過undo記錄的scn找回原紀錄
- Python中open函式怎麼操作檔案Python函式
- C#是怎麼跑起來的C#
- 面試官：為什麼 Java 不把基本型別放在堆中？我竟然答不上來。。面試Java型別
- oracle的redo和undoOracle
- undo_retention的作用
- oracle rowid詳解Oracle