Oracle UNDO块(3)
发布时间:2021-05-14 16:25 所属栏目:[站长百科] 来源:网络整理
导读:? *-----------------------------* Rec #0x20 slt: 0x19 objn: 73181(0x00011ddd) objd: 73181 tblspc: 4(0x00000004)* Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split:
? *----------------------------- * Rec #0x20 slt: 0x19 objn: 73181(0x00011ddd) objd: 73181 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: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x0180000c.0003.1d ctl max scn: 0x0000.00104368 prv tx scn: 0x0000.0010436a txn start scn: scn: 0x0000.0010444f logon user: 0 prev brb: 25165834 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000097 hdba: 0x01000092 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191 ncol: 8 nnew: 1 size: 0 Vector content: col 5: [ 2] c2 09? 计算最后一行col的raw值 [email?protected] prod>select utl_raw.cast_to_number(replace(‘C2 09‘,‘ ‘)) from dual; UTL_RAW.CAST_TO_NUMBER(REPLACE(‘C209‘,‘‘)) ------------------------------------------ 800? 800正是更新前的值。Oracle就是这样存储数据前镜像的。? 可以看到在record中也记录了该undo对应的前镜像的数据块的地址信息bdba 0x01000097。我们进行转换得到:0000000100?0000000000000010010111 可以得到是file 4,block 151。 [email?protected] prod>select a.table_name,a.tablespace_name,b.file_name from dba_tables a,dba_data_files b where a.tablespace_name=b.tablespace_name and b.file_id = ‘4‘; TABLE_NAME TABLESPACE_NAME FILE_NAME ------------------------------ ------------------------------ ---------------------------------------------------------------------- T2 USERS /u01/oradata/prod/users01.dbf T1 USERS /u01/oradata/prod/users01.dbf EMP1 USERS /u01/oradata/prod/users01.dbf TEAM USERS /u01/oradata/prod/users01.dbf CUSTOMERS USERS /u01/oradata/prod/users01.dbf DIMENSION_EXCEPTIONS USERS /u01/oradata/prod/users01.dbf SUBCATEGORY_REF_LIST_NESTEDTAB USERS /u01/oradata/prod/users01.dbf PRODUCT_REF_LIST_NESTEDTAB USERS /u01/oradata/prod/users01.dbf SALGRADE USERS /u01/oradata/prod/users01.dbf BONUS USERS /u01/oradata/prod/users01.dbf EMP USERS /u01/oradata/prod/users01.dbf DEPT USERS /u01/oradata/prod/users01.dbf 12 rows selected.? 转储数据块 [email?protected] prod>alter system dump datafile 4 block 151; System altered. users01.dbf 转储信息如下: Block header dump: 0x01000097 Object id on Block? Y seg/obj: 0x11ddd csc: 0x00.f587b itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0001.010.00000198 0x00c15ab0.0061.12 C--- 0 scn 0x0000.000b6f31 0x02 0x000b.019.0000000a 0x0180000c.0003.23 ---- 4 fsc 0x0000.00000000 bdba: 0x01000097 data_block_dump,data header at 0xe51664? 当我们修改数据的时候会对相应的数据加锁,更直接说该锁存在于数据块中,并且存在itl(事务槽信息),itl的详细内容:其中包括xid,uba,flag,lock status,scn uba,这正是该数据的前镜像信息。 我们查看一下事务视图进行一下确认。 [email?protected] prod>select xidusn,xid,name,ubasqn,ubarec,ubablk from v$transaction where xidusn=11; XIDUSN XIDSLOT XID NAME UBASQN UBAREC UBAFIL UBABLK ---------- ---------- -------------------- -------------------- ---------- ---------- ---------- ---------- 11 25 0B0019000A000000 3 35 6 12? 事务的信息 [email?protected] prod>select xid,start_scn,commit_scn,operation,table_name,row_id,undo_sql from flashback_transaction_query where xid=‘0B0019000A000000‘; XID START_SCN COMMIT_SCN OPERATION TABLE_NAME ROW_ID UNDO_SQL -------------------- ---------- ---------- -------------------------------- ---------- ------------------- ---------- 0B0019000A000000 1066063 UNKNOWN EMP 0B0019000A000000 1066063 UNKNOWN EMP 0B0019000A000000 1066063 UNKNOWN EMP 0B0019000A000000 1066063 UNKNOWN EMP 0B0019000A000000 1066063 BEGIN? 摘录一下undo header事务表0x19(25槽)如下 0x19 10 0x80 0x000a 0x0000 0x0000.0010444f 0x0180000c 0x0000.000.00000000 0x00000001 0x00000000 0? 在数据块中的itl中存在一个uba,这正是该数据的前镜像信息。 当执行个update语句的时候,首先在segment上分配段分配slot,然后再数据块头部记录itl信息指向undo record,undo record记录了数据的前镜像, [email?protected] prod>SELECT * FROM V$MYSTAT WHERE ROWNUM<2; SID STATISTIC# VALUE ---------- ---------- ---------- 37 0 0? ? [email?protected] prod>col DESCRIPTION for a50 [email?protected] prod>col TYPE for a20 [email?protected] prod>select a.sid,a.lmode,a.id1,b.type,b.name,b.description from v$lock a,v$lock_type b where a.sid=37 and a.type=b.type; SID LMODE ID1 TYPE NAME DESCRIPTION ---------- ---------- ---------- -------------------- -------------------- -------------------------------------------------- 37 4 100 AE Edition Lock Prevent Dropping an edition in use 【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。 |
相关内容
推荐文章
热点阅读