[20181206]关于一致性读取3.txt

来源：这里教程网时间：2026-03-03 12:43:07 作者：

[20181206]关于一致性读取3.txt --//简单演示一致性读取以及如何读取undo重构数据块的.我不想转储对应的undo块,解析那些复杂的过程. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//以scott用户登录,执行如下: create table t (id number,name varchar2(10)); insert into t select 1 id ,lpad('A',10,'A') name from dual ; commit ; --//分析略. SCOTT@book> select rowid,t.* from t; ROWID ID NAME ------------------ ---------- -------------------- AAAWGdAAEAAAAIMAAA 1 AAAAAAAAAA SCOTT@book> @ rowid AAAWGdAAEAAAAIMAAA OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 90525 4 524 0 0x100020C 4,524 alter system dump datafile 4 block 524 ; SQL> alter system set "_smu_debug_mode" = 45; System altered. --//可以指定回滚段.要重启才生效. --//说明之所以这样,主要我发现执行dml使用的回滚段会使用相同的块. 2.建立测试脚本: $ cat b1.txt var x refcursor; set numw 12 select current_scn from v$database; exec open :x for select * from t where id=1; pause alter system flush buffer_cache; alter system flush buffer_cache; @ 10046on 12 print :x @ 10046off @ pp quit $ cat b4.txt set linesize 300 column name new_value v_rollname column name noprint select name from v$rollname where usn=1; set transaction use rollback segment "&&v_rollname"; update t set name=lpad('B',9,'B') where id=1; @ xid commit ; select name from v$rollname where usn=2; set transaction use rollback segment "&&v_rollname"; update t set name=lpad('C',8,'C') where id=1; @ xid commit; select name from v$rollname where usn=3; set transaction use rollback segment "&&v_rollname"; update t set name=lpad('D',7,'D') where id=1; @ xid commit ; select name from v$rollname where usn=4; set transaction use rollback segment "&&v_rollname"; update t set name=lpad('E',6,'E') where id=1; @ xid commit ; column name print 3.执行脚本: SCOTT@book> @ b1.txt CURRENT_SCN ------------ 13815694556 PL/SQL procedure successfully completed. --//这里暂停执行脚本 b4.txt System altered. System altered. old 1: alter session set events '10046 trace name context forever, level &1' new 1: alter session set events '10046 trace name context forever, level 12' Session altered. ID NAME ------------ -------------------- 1 AAAAAAAAAA Session altered. TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38984.trc SCOTT@book> @ b4.txt old 1: set transaction use rollback segment "&&v_rollname" new 1: set transaction use rollback segment "_SYSSMU1_3724004606$" Transaction set. 1 row updated. XIDUSN_XIDSLOT_XIDSQN ------------------------------ 1.22.1560 C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ---------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU1_3724004606$' XID 1 22 1560; 1 22 1560 3 1356 768 11 ACTIVE 1 1 0100160018060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU1_3724004606$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1356; Commit complete. old 1: set transaction use rollback segment "&&v_rollname" new 1: set transaction use rollback segment "_SYSSMU2_2996391332$" Transaction set. 1 row updated. XIDUSN_XIDSLOT_XIDSQN ------------------------------ 2.24.1784 C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ---------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 24 1784; 2 24 1784 3 1217 559 46 ACTIVE 1 1 02001800F8060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1217; Commit complete. old 1: set transaction use rollback segment "&&v_rollname" new 1: set transaction use rollback segment "_SYSSMU3_1723003836$" Transaction set. 1 row updated. XIDUSN_XIDSLOT_XIDSQN ------------------------------ 3.18.1777 C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ---------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_1723003836$' XID 3 18 1777; 3 18 1777 3 1010 921 52 ACTIVE 1 1 03001200F1060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1010; Commit complete. old 1: set transaction use rollback segment "&&v_rollname" new 1: set transaction use rollback segment "_SYSSMU4_1254879796$" Transaction set. 1 row updated. XIDUSN_XIDSLOT_XIDSQN ------------------------------ 4.11.1571 C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ---------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU4_1254879796$' XID 4 11 1571; 4 11 1571 3 179 811 45 ACTIVE 1 1 04000B0023060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU4_1254879796$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 179; Commit complete. --//使用的回滚段保存前映像的块的dba是(3,179) (3,1010) (3,1217) (3,1356) [注:从尾部向前看.] 4.分析转储: $ grep -i "nam='db file" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38984.trc WAIT #140008354428832: nam='db file sequential read' ela= 12 file#=4 block#=522 blocks=1 obj#=90525 tim=1544057363227135 --//读数据段段头. WAIT #140008354428832: nam='db file scattered read' ela= 27 file#=4 block#=523 blocks=5 obj#=90525 tim=1544057363227276 --//读数据块,注意blocks=5,包括了dba=4,524. WAIT #140008354428832: nam='db file sequential read' ela= 10 file#=3 block#=179 blocks=1 obj#=0 tim=1544057363227374 --//修改lpad('E',6,'E')的事务. WAIT #140008354428832: nam='db file sequential read' ela= 11 file#=3 block#=1010 blocks=1 obj#=0 tim=1544057363227490 --//修改lpad('D',7,'D')的事务. WAIT #140008354428832: nam='db file sequential read' ela= 8 file#=3 block#=1217 blocks=1 obj#=0 tim=1544057363227553 --//修改lpad('C',6,'C')的事务 WAIT #140008354428832: nam='db file sequential read' ela= 17 file#=3 block#=1356 blocks=1 obj#=0 tim=1544057363227624 --//修改lpad('B',6,'B')的事务. SYS@book> @ bh 4 524 HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME ---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- 0000000084D68628 4 524 1 data block cr 1 930792668 3 3 179 811 0000000076B3C000 T 0000000084D68628 4 524 1 data block xcur 0 0 0 0 0 0 0000000078784000 T --//看看state='cr',ba=0000000063C2A000地址指向的块信息: SYS@book> oradebug setmypid Statement processed. SYS@book> oradebug peek 0x76B3C000 8192 1 [076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 00000001 0001619D 377AC4F1 00000003 00320002 01000208 0018000A 000052D1 00C001B1 ... SYS@book> oradebug tracefile_name /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39054.trc --//检查转储: *** 2018-12-06 08:53:44.097 Processing Oradebug command 'peek 0x76B3C000 8192 1' [076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 ... Dump of memory from 0x076B3C014 to 0x076B3E000 076B3C010 00000001 0001619D 377AC4F1 [.....a....z7] 076B3C020 00000003 00320002 01000208 0018000A [......2.........] 076B3C030 000052D1 00C001B1 00070F3B 00038000 [.R......;.......] 076B3C040 377AC304 00000000 00000000 00000000 [..z7............] 076B3C050 00000000 00000000 00000000 00000000 [................] 076B3C060 00000000 00010100 0014FFFF 1F731F0F [..............s.] 076B3C070 00001F73 1F0F0001 00000000 00000000 [s...............] 076B3C080 00000000 00000000 00000000 00000000 [................] Repeat 494 times 076B3DF70 2C000000 C1020200 41410A02 41414141 [...,......AAAAAA] 076B3DF80 41414141 0202002C 420902C1 42424242 [AAAA,......BBBBB] 076B3DF90 42424242 0202002C 430802C1 43434343 [BBBB,......CCCCC] 076B3DFA0 2C434343 C1020200 44440702 44444444 [CCC,......DDDDDD] 076B3DFB0 02012C44 0602C102 45454545 002C4545 [D,......EEEEEE,.] 076B3DFC0 02C10202 44444407 44444444 0202002C [.....DDDDDDD,...] 076B3DFD0 430802C1 43434343 2C434343 C1020200 [...CCCCCCCC,....] 076B3DFE0 42420902 42424242 2C424242 C1020200 [..BBBBBBBBB,....] 076B3DFF0 41410A02 41414141 41414141 C5210600 [..AAAAAAAAAA..!.] *** 2018-12-06 08:53:44.098 Oradebug command 'peek 0x76B3C000 8192 1' console output: [076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 00000001 0001619D 377AC4F1 00000003 00320002 01000208 0018000A 000052D1 00C001B1 ... --//你可以发现一致性读取如何通过undo重构数据块的信息,就好像反转操作.由于我每次修改长度都不等于原来的长度.这样看到转储的信息包含A,B,C,D,E等字符串2遍. --//换一种方式转储数据缓存的对应数据块dba=4,524: SYS@book> @ bbvi 4 524 BVI_COMMAND ---------------------------------------------------------------------------------------------------- bvi -b 4292608 -s 8192 /mnt/ramdisk/book/users01.dbf xxd -c16 -g 2 -s 4292608 -l 8192 /mnt/ramdisk/book/users01.dbf dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=524 count=1 of=4_524.dd conv=notrunc 2>/dev/null od -j 4292608 -N 8192 -t x1 -v /mnt/ramdisk/book/users01.dbf hexdump -s 4292608 -n 8192 -C -v /mnt/ramdisk/book/users01.dbf alter system dump datafile '/mnt/ramdisk/book/users01.dbf' block 524; alter session set events 'immediate trace name set_tsn_p1 level 5'; alter session set events 'immediate trace name buffer level 16777740'; 9 rows selected. SYS@book> alter session set events 'immediate trace name set_tsn_p1 level 5'; Session altered. SYS@book> alter session set events 'immediate trace name buffer level 16777740'; Session altered. BH (0x76bf5770) file#: 4 rdba: 0x0100020c (4/524) class: 1 ba: 0x76b3c000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//ba地址与前面看到的一致. set: 65 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 18,19 dbwrid: 1 obj: 90525 objn: 90525 tsn: 4 afn: 4 hint: f hash: [0x787f8408,0x84d69f20] lru: [0x84bd22c0,0x783dbee0] ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL tch: 1 cr: [scn: 0x3.377ac4dc],[xid: 0x4.b.623],[uba: 0xc000b3.32b.2d],[cls: 0x3.377ac520],[sfl: 0x1],[lc: 0x3.377ac4f2] flags: only_sequential_access buffer tsn: 4 rdba: 0x0100020c (4/524) scn: 0x0003.377ac521 seq: 0x00 flg: 0x00 tail: 0xc5210600 frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x0000000076B3C000 to 0x0000000076B3E000 076B3C000 0000A206 0100020C 377AC521 00000003 [........!.z7....] 076B3C010 00000000 00000001 0001619D 377AC4F1 [.........a....z7] 076B3C020 00000003 00320002 01000208 0018000A [......2.........] 076B3C030 000052D1 00C001B1 00070F3B 00038000 [.R......;.......] 076B3C040 377AC304 00000000 00000000 00000000 [..z7............] 076B3C050 00000000 00000000 00000000 00000000 [................] 076B3C060 00000000 00010100 0014FFFF 1F731F0F [..............s.] 076B3C070 00001F73 1F0F0001 00000000 00000000 [s...............] 076B3C080 00000000 00000000 00000000 00000000 [................] Repeat 494 times 076B3DF70 2C000000 C1020200 41410A02 41414141 [...,......AAAAAA] 076B3DF80 41414141 0202002C 420902C1 42424242 [AAAA,......BBBBB] 076B3DF90 42424242 0202002C 430802C1 43434343 [BBBB,......CCCCC] 076B3DFA0 2C434343 C1020200 44440702 44444444 [CCC,......DDDDDD] 076B3DFB0 02012C44 0602C102 45454545 002C4545 [D,......EEEEEE,.] 076B3DFC0 02C10202 44444407 44444444 0202002C [.....DDDDDDD,...] 076B3DFD0 430802C1 43434343 2C434343 C1020200 [...CCCCCCCC,....] 076B3DFE0 42420902 42424242 2C424242 C1020200 [..BBBBBBBBB,....] 076B3DFF0 41410A02 41414141 41414141 C5210600 [..AAAAAAAAAA..!.] Block header dump: 0x0100020c Object id on Block? Y seg/obj: 0x1619d csc: 0x03.377ac4f1 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.018.000052d1 0x00c001b1.0f3b.07 C--- 0 scn 0x0003.377ac304 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x0100020c data_block_dump,data header at 0x76b3c064 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x76b3c064 76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f0f avsp=0x1f73 tosp=0x1f73 0xe:pti[0] nrow=1 offs=0 0x12:pri[0] offs=0x1f0f block_row_dump: tab 0, row 0, @0x1f0f --//这个偏移与第1次插入的问题不同. tl: 17 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 02 col 1: [10] 41 41 41 41 41 41 41 41 41 41 end_of_block_dump 4.换一种方式: $ cat b5.txt alter system flush buffer_cache; alter system flush buffer_cache; @ 10046on 12 select * from t as of scn 13815694556 where id=1; @ 10046off @ pp SCOTT@book> @ b5.txt System altered. System altered. old 1: alter session set events '10046 trace name context forever, level &1' new 1: alter session set events '10046 trace name context forever, level 12' Session altered. ID NAME ---------- -------------------- 1 AAAAAAAAAA Session altered. TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39134.trc $ grep -i "nam='db file" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39134.trc WAIT #140406431959032: nam='db file scattered read' ela= 45 file#=2 block#=416 blocks=8 obj#=272 tim=1544058316954648 WAIT #140406431959032: nam='db file scattered read' ela= 48 file#=2 block#=74816 blocks=8 obj#=272 tim=1544058316954781 WAIT #140406431959032: nam='db file scattered read' ela= 52 file#=2 block#=71376 blocks=8 obj#=270 tim=1544058316954972 --//上面是读以下部分访问的数据块与前面测试一直. WAIT #140406431960096: nam='db file sequential read' ela= 11 file#=4 block#=522 blocks=1 obj#=90525 tim=1544058316956075 WAIT #140406431960096: nam='db file scattered read' ela= 38 file#=4 block#=523 blocks=5 obj#=90525 tim=1544058316956220 WAIT #140406431960096: nam='db file sequential read' ela= 17 file#=3 block#=179 blocks=1 obj#=0 tim=1544058316956314 WAIT #140406431960096: nam='db file sequential read' ela= 10 file#=3 block#=1010 blocks=1 obj#=0 tim=1544058316956431 WAIT #140406431960096: nam='db file sequential read' ela= 10 file#=3 block#=1217 blocks=1 obj#=0 tim=1544058316956497 WAIT #140406431960096: nam='db file sequential read' ela= 9 file#=3 block#=1356 blocks=1 obj#=0 tim=1544058316956558 --//前面的3条访问的数据块是: SCOTT@book> @ which_obj 2 416 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------ SYS SMON_SCN_TIME_SCN_IDX INDEX SYSAUX 0 2 416 65536 8 2 SCOTT@book> @ which_obj 2 74816 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------ SYS SMON_SCN_TIME_SCN_IDX INDEX SYSAUX 1 2 74816 65536 8 2 SCOTT@book> @ which_obj 2 71376 OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------ SYS SMON_SCN_TO_TIME_AUX CLUSTER SYSAUX 17 2 71296 1048576 128 2 --//如果建立唯一索引在id字段,你再重复前面的测试,你会发现结果不同,大家可以参考我以前写的: --//链接:http://blog.itpub.net/267265/viewspace-772101/ --//附上测试脚本: $ cat xid.sql column XIDUSN_XIDSLOT_XIDSQN format a30 select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ; --select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction; SELECT 'ALTER SYSTEM DUMP UNDO BLOCK ''' || (SELECT name FROM sys.undo$ WHERE us# = xidusn) || ''' XID ' || xidusn || ' ' || xidslot || ' ' || xidsqn || ';' || CHR (10) || 'ALTER SYSTEM DUMP UNDO HEADER ''' || (SELECT name FROM sys.undo$ WHERE us# = xidusn) || '''' || ';' || CHR (10) || 'ALTER SYSTEM DUMP DATAFILE ' || UBAFIL || ' BLOCK ' || UBABLK || ';' c70 ,XIDUSN ,XIDSLOT ,XIDSQN ,UBAFIL ,UBABLK ,UBASQN ,UBAREC ,STATUS ,used_ublk ,USED_UREC ,xid ,ADDR ,START_DATE ,flag FROM v$transaction; $ cat bbvi.sql column bvi_command format a100 select 'bvi -b '|| &2*BLOCK_SIZE||' -s '||block_size||' '||NAME bvi_command from v$datafile where file#=&1 union all select 'xxd -c16 -g 2 -s '|| &2*BLOCK_SIZE||' -l '||block_size||' '||NAME bvi_command from v$datafile where file#=&1 union all select 'dd if='||name||' bs='||block_size||' skip='||&2||' count=1 of='||&1||'_'||&2||'.dd conv=notrunc 2>/dev/null' bvi_command from v$datafile where file#=&1 union all select 'od -j '||&2*BLOCK_SIZE||' -N '||block_size||' -t x1 -v '||name bvi_command from v$datafile where file#=&1 union all select 'hexdump -s '||&2*BLOCK_SIZE||' -n '||block_size||' -C -v '||name bvi_command from v$datafile where file#=&1 union all select 'alter system dump datafile '||''''||name||''''||' block '||&2||';' from v$datafile where file#=&1 union all select '' bvi_command from dual union all select 'alter session set events ''immediate trace name set_tsn_p1 level '|| to_char(ts#+1)||''';' bvi_command from sys.ts$ where name in (select TABLESPACE_NAME from v$datafile_header where FILE# = &&1) union all select 'alter session set events ''immediate trace name buffer level '||dbms_utility.make_data_block_address(&&1,&&2)||''';' bvi_command from dual;

编辑推荐：

返回列表

[20181206]关于一致性读取3.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推