背景
在学习ORACLE概念中事务及LOCK锁的知识时,需要了解一下事务表transaction table的概念,实战出真知,通过实验来梳理下这些知识,争取有个全局的理解。
结论
1, 数据库版本为11G2,事务表是存储在UNDO SEGMENT HEADER BLOCK
3, 转储UNDO SEGMENT HEADER BLOCK的语法为alter system dump datafile 7 block 528;
4,每产生一个事务对应事务表的一个条目
5,事务表的条目包括内容:
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x14 10 0x80 0x0206 0x0000 0x0000.01337761 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 0 ---这就是当前事务的在事务表中的条目
6,事务表的条目scn及dba对应修改表记录对应数据块中的scn及itl uba第一部分
7,事务提交后数据块的SCN及ITL仍是提交前的SCN及ITL没有发生变化
8,发生检查点后事务表条目发生变化,但数据块SCN及ITL仍没有发生变化
9,提交事务后对修改的记录进行SQL查询,发现数据块SCN及ITL仍没有发生变化(本意是形成一个延迟块清除)
10,延迟块清除的概念暂不清晰,需要在另一文章进行学习
测试
1,数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2,创建测试表
SQL> create table t_transaction(a int);
Table created.
3,插入记录不提交
SQL> insert into t_transaction values(1);
1 row created.
4,转储undo segment header,获知事务表的信息
SQL> select count(*) from v$transaction;
COUNT(*)
----------
1
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
_SYSSMU180_1681773623$ 7 528
SQL> select pid,spid from v$process where addr='00000000DD653880';
PID SPID
---------- ------------------------------------------------
179 5498
SQL> show user
USER is "SYS"
SQL> alter session set tracefile_identifier='undo_test';
Session altered.
SQL> alter system dump datafile 7 block 528;
System altered.
[oracle@seconary trace]$ pwd
/oracle/diag/rdbms/guowang/guowang/trace
[oracle@seconary trace]$ ll -l *undo*
-rw-r----- 1 oracle oinstall 17115 Oct 17 15:56 guowang_ora_5877_undo_test.trc
可见TRC文件有有事务表及扩展事务表2部分
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0206 0x0002 0x0000.01336fc8 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x01 9 0x00 0x0206 0x0000 0x0000.01336f7f 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x02 9 0x00 0x0206 0x0004 0x0000.01337033 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x03 9 0x00 0x0206 0x000c 0x0000.0133716d 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x04 9 0x00 0x0206 0x0006 0x0000.013370b5 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110526
0x05 9 0x00 0x0206 0x0008 0x0000.01337229 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110531
0x06 9 0x00 0x0206 0x0003 0x0000.0133712b 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x07 9 0x00 0x0206 0x000d 0x0000.0133739d 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x08 9 0x00 0x0206 0x000a 0x0000.013372ab 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110531
0x09 9 0x00 0x0206 0x0007 0x0000.0133735a 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x0a 9 0x00 0x0206 0x0009 0x0000.01337317 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x0b 9 0x00 0x0206 0x000e 0x0000.01337507 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x0c 9 0x00 0x0206 0x0005 0x0000.013371b9 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x0d 9 0x00 0x0206 0x000f 0x0000.013373ee 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110536
0x0e 9 0x00 0x0206 0x0012 0x0000.01337549 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x0f 9 0x00 0x0206 0x0010 0x0000.0133746f 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110536
0x10 9 0x00 0x0206 0x000b 0x0000.013374c6 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x11 9 0x00 0x0206 0xffff 0x0000.013376f3 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 1445110545
0x12 9 0x00 0x0206 0x0015 0x0000.0133765c 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110542
0x13 9 0x00 0x0205 0x0019 0x0000.01336b6d 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110512
0x14 10 0x80 0x0206 0x0000 0x0000.01337761 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 0 ---这就是当前事务的在事务表中的条目
0x15 9 0x00 0x0206 0x0011 0x0000.013376a1 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 1445110545
0x16 9 0x00 0x0205 0x0017 0x0000.01336a6c 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110510
0x17 9 0x00 0x0205 0x0013 0x0000.01336aec 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110511
0x18 9 0x00 0x0205 0x001a 0x0000.01336c0b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x19 9 0x00 0x0205 0x0018 0x0000.01336bc9 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x1a 9 0x00 0x0205 0x001b 0x0000.01336c5b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x1b 9 0x00 0x0205 0x001c 0x0000.01336cdb 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110516
0x1c 9 0x00 0x0205 0x0020 0x0000.01336d5b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110517
0x1d 9 0x00 0x0205 0x0021 0x0000.01336de8 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
0x1e 9 0x00 0x0205 0x0001 0x0000.01336f32 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110522
0x1f 9 0x00 0x0205 0x001e 0x0000.01336eb3 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110521
0x20 9 0x00 0x0205 0x001d 0x0000.01336da1 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
0x21 9 0x00 0x0205 0x001f 0x0000.01336e35 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
EXT TRN CTL::
usn: 180
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x7fff00000000 sp7:0x2b6d00000000 sp8:0x2b6d00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
End dump data blocks tsn: 11 file#: 7 minblk 528 maxblk 528
[oracle@seconary trace]$
5,了解下事务表中上述未提交事务的条目的内容到底是什么?
先DUMP下事务涉及记录的数据块信息
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t_transaction;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 98903
SQL> alter session set tracefile_identifier='block_test';
Session altered.
SQL> alter system dump datafile 4 block 98903;
System altered.
[oracle@seconary trace]$ pwd
/oracle/diag/rdbms/guowang/guowang/trace
[oracle@seconary trace]$ ll *block*
-rw-r----- 1 oracle oinstall 3272 Oct 17 16:10 guowang_ora_7124_block_test.trc
BH (0x9af66868) file#: 4 rdba: 0x01018257 (4/98903) class: 1 ba: 0x9a010000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 217,28
dbwrid: 0 obj: 74389 objn: 74389 tsn: 4 afn: 4 hint: f
hash: [0xda3552c0,0xda3552c0] lru: [0x9af66a80,0xb9fab030]
ckptq: [NULL] fileq: [NULL] objq: [0x9af66aa8,0xd289ebd8]
st: XCURRENT md: NULL tch: 2
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x01018257 (4/98903)
scn: 0x0000.01337761 seq: 0x03 flg: 0x04 tail: 0x77610603
frmt: 0x02 chkval: 0x0bcc type: 0x06=trans data
Object id on Block? Y
seg/obj: 0x12295 csc: 0x00.1337761 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1018250 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00b4.014.00000206 0x01c00215.0078.04 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01018257
可见事务表中事务对应条目中的scn正好和修改表中记录所属数据块scn相同
[oracle@seconary trace]$ more guowang_ora_7124_block_test.trc|grep -i --color scn
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
scn: 0x0000.01337761 seq: 0x03 flg: 0x04 tail: 0x77610603
Itl Xid Uba Flag Lck Scn/Fsc
可见事务表中事务对应条目中的dba对应修改表中记录所属数据块中的itl的uba第一部分(uba分为以.号分割的3部分)
[oracle@seconary trace]$ more guowang_ora_7124_block_test.trc|grep -i --color 0x01c00215
0x01 0x00b4.014.00000206 0x01c00215.0078.04 ---- 1 fsc 0x0000.00000000
[oracle@seconary trace]$
6,提交事务
SQL> commit;
Commit complete.
7,再次DUMP事务表及涉及数据块的内容
可见事务提交后,事务表对应条目并没有变化
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0206 0x0002 0x0000.01336fc8 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x01 9 0x00 0x0206 0x0000 0x0000.01336f7f 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x02 9 0x00 0x0206 0x0004 0x0000.01337033 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110525
0x03 9 0x00 0x0206 0x000c 0x0000.0133716d 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x04 9 0x00 0x0206 0x0006 0x0000.013370b5 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110526
0x05 9 0x00 0x0206 0x0008 0x0000.01337229 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110531
0x06 9 0x00 0x0206 0x0003 0x0000.0133712b 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x07 9 0x00 0x0206 0x000d 0x0000.0133739d 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x08 9 0x00 0x0206 0x000a 0x0000.013372ab 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110531
0x09 9 0x00 0x0206 0x0007 0x0000.0133735a 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x0a 9 0x00 0x0206 0x0009 0x0000.01337317 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110535
0x0b 9 0x00 0x0206 0x000e 0x0000.01337507 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x0c 9 0x00 0x0206 0x0005 0x0000.013371b9 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110530
0x0d 9 0x00 0x0206 0x000f 0x0000.013373ee 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110536
0x0e 9 0x00 0x0206 0x0012 0x0000.01337549 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x0f 9 0x00 0x0206 0x0010 0x0000.0133746f 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110536
0x10 9 0x00 0x0206 0x000b 0x0000.013374c6 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110540
0x11 9 0x00 0x0206 0xffff 0x0000.013376f3 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 1445110545
0x12 9 0x00 0x0206 0x0015 0x0000.0133765c 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110542
0x13 9 0x00 0x0205 0x0019 0x0000.01336b6d 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110512
0x14 10 0x80 0x0206 0x0000 0x0000.01337761 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 0
0x15 9 0x00 0x0206 0x0011 0x0000.013376a1 0x01c00215 0x0000.000.00000000 0x00000001 0x00000000 1445110545
0x16 9 0x00 0x0205 0x0017 0x0000.01336a6c 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110510
0x17 9 0x00 0x0205 0x0013 0x0000.01336aec 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110511
0x18 9 0x00 0x0205 0x001a 0x0000.01336c0b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x19 9 0x00 0x0205 0x0018 0x0000.01336bc9 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x1a 9 0x00 0x0205 0x001b 0x0000.01336c5b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110515
0x1b 9 0x00 0x0205 0x001c 0x0000.01336cdb 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110516
0x1c 9 0x00 0x0205 0x0020 0x0000.01336d5b 0x01c00213 0x0000.000.00000000 0x00000001 0x00000000 1445110517
0x1d 9 0x00 0x0205 0x0021 0x0000.01336de8 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
0x1e 9 0x00 0x0205 0x0001 0x0000.01336f32 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110522
0x1f 9 0x00 0x0205 0x001e 0x0000.01336eb3 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110521
0x20 9 0x00 0x0205 0x001d 0x0000.01336da1 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
0x21 9 0x00 0x0205 0x001f 0x0000.01336e35 0x01c00214 0x0000.000.00000000 0x00000001 0x00000000 1445110520
可见数据块的scn也没有变化,并且ITL也没有变化
[oracle@seconary trace]$ more guowang_ora_7960_block_test1.trc|grep -i --color scn
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0x0.13719f9] HSUB: [1]
scn: 0x0000.01337761 seq: 0x03 flg: 0x04 tail: 0x77610603
Itl Xid Uba Flag Lck Scn/Fsc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00b4.014.00000206 0x01c00215.0078.04 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01018257
8,如果发生一个检查点动作,再看下事务表的条目及修改数据块的scn及相关内容
SQL> alter system checkpoint;
System altered.
发生检查点后,事务表条目发生了变化,state及scn都变化了,dba也变化了
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
*** 2015-10-17 16:33:51.699
0x00 9 0x00 0x0218 0x0002 0x0000.0137f9c6 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113896
0x01 9 0x00 0x0218 0x0000 0x0000.0137f988 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113896
0x02 9 0x00 0x0218 0x0004 0x0000.0137fa10 0x01c0021f 0x0000.000.00000000 0x00000002 0x00000000 1445113896
0x03 9 0x00 0x0218 0x000c 0x0000.0137fb57 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113902
0x04 9 0x00 0x0218 0x0006 0x0000.0137fa74 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113897
0x05 9 0x00 0x0218 0x0008 0x0000.0137fbe2 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113902
0x06 9 0x00 0x0218 0x0003 0x0000.0137faef 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113898
0x07 9 0x00 0x0218 0x000d 0x0000.0137fd55 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113907
0x08 9 0x00 0x0218 0x000a 0x0000.0137fc5d 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113903
0x09 9 0x00 0x0218 0x0007 0x0000.0137fd12 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113907
0x0a 9 0x00 0x0218 0x0009 0x0000.0137fcd3 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113907
0x0b 9 0x00 0x0218 0x000e 0x0000.0137fefb 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113912
0x0c 9 0x00 0x0218 0x0005 0x0000.0137fb95 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113902
0x0d 9 0x00 0x0218 0x000f 0x0000.0137fda0 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113907
0x0e 9 0x00 0x0218 0x0012 0x0000.0137ff37 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113912
0x0f 9 0x00 0x0218 0x0010 0x0000.0137fe13 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113908
0x10 9 0x00 0x0218 0x000b 0x0000.0137fe8d 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113909
0x11 9 0x00 0x0218 0x0014 0x0000.01380054 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113913
0x12 9 0x00 0x0218 0x0015 0x0000.0137ff75 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113912
0x13 9 0x00 0x0218 0x0019 0x0000.0138016b 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113918
0x14 9 0x00 0x0218 0x0016 0x0000.013800a3 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113917 ----
0x15 9 0x00 0x0218 0x0011 0x0000.0137ffde 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113913
0x16 9 0x00 0x0218 0x0017 0x0000.013800ee 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113917
0x17 9 0x00 0x0218 0x0013 0x0000.0138012d 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113917
0x18 9 0x00 0x0218 0xffff 0x0000.0138022f 0x01c00211 0x0000.000.00000000 0x00000002 0x00000000 1445113919
0x19 9 0x00 0x0218 0x0018 0x0000.013801ba 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445113918
0x1a 9 0x00 0x0217 0x001b 0x0000.0137f693 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113886
0x1b 9 0x00 0x0217 0x001c 0x0000.0137f70a 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113887
0x1c 9 0x00 0x0217 0x0020 0x0000.0137f782 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113888
0x1d 9 0x00 0x0217 0x0021 0x0000.0137f81d 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113891
0x1e 9 0x00 0x0217 0x0001 0x0000.0137f947 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113896
0x1f 9 0x00 0x0217 0x001e 0x0000.0137f8ee 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113892
0x20 9 0x00 0x0217 0x001d 0x0000.0137f7df 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113891
0x21 9 0x00 0x0217 0x001f 0x0000.0137f872 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445113891
发生检查点后数据块的scn没有发生变化
[oracle@seconary trace]$ more guowang_ora_8403_block_testhaha.trc|grep -i --color scn
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0x0.13719f9] HSUB: [1]
scn: 0x0000.01337761 seq: 0x03 flg: 0x04 tail: 0x77610603
Itl Xid Uba Flag Lck Scn/Fsc
发生检查点后数据块的itl没有发生变化
Block header dump: 0x01018257
Object id on Block? Y
seg/obj: 0x12295 csc: 0x00.1337761 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1018250 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00b4.014.00000206 0x01c00215.0078.04 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01018257
9,对已提交的事务的数据块的表记录进行查询后,再次查看事务表及数据块的相关内容
SQL> conn scott/system
Connected.
SQL> select count(*) from t_transaction;
COUNT(*)
----------
1
数据块的相关内容没有发生变化
[oracle@seconary trace]$ more guowang_ora_8761_block_testtop.trc|grep -i --color scn
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0x0.13719f9] HSUB: [1]
scn: 0x0000.01337761 seq: 0x03 flg: 0x04 tail: 0x77610603
Itl Xid Uba Flag Lck Scn/Fsc
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00b4.014.00000206 0x01c00215.0078.04 ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0231 0x0002 0x0000.013937b2 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114811
0x01 9 0x00 0x0231 0x0000 0x0000.01393745 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114811
0x02 9 0x00 0x0231 0x0006 0x0000.0139381c 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114815
0x03 9 0x00 0x0231 0x0005 0x0000.0139390c 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114816
0x04 9 0x00 0x0231 0x000c 0x0000.01393890 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114815
0x05 9 0x00 0x0231 0x0008 0x0000.01393966 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114817
0x06 9 0x00 0x0231 0x0004 0x0000.01393853 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114815
0x07 9 0x00 0x0231 0x000d 0x0000.01393a83 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114822
0x08 9 0x00 0x0231 0x000a 0x0000.013939c1 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114817
0x09 9 0x00 0x0231 0x0007 0x0000.01393a43 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114822
0x0a 9 0x00 0x0231 0x0009 0x0000.01393a08 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114822
0x0b 9 0x00 0x0231 0x0012 0x0000.01393c2e 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114828
0x0c 9 0x00 0x0231 0x0003 0x0000.013938c6 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114816
0x0d 9 0x00 0x0231 0x000f 0x0000.01393adf 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114823
0x0e 9 0x00 0x0231 0x0010 0x0000.01393b76 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114823
0x0f 9 0x00 0x0231 0x000e 0x0000.01393b32 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114823
0x10 9 0x00 0x0231 0x000b 0x0000.01393bea 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114828
0x11 9 0x00 0x0231 0x0014 0x0000.01393d0e 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114829
0x12 9 0x00 0x0231 0x0015 0x0000.01393c76 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114828
0x13 9 0x00 0x0231 0x0019 0x0000.01393e8d 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114834
0x14 9 0x00 0x0231 0x0016 0x0000.01393d62 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114830 ---scn又变化了
0x15 9 0x00 0x0231 0x0011 0x0000.01393cbe 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114829
0x16 9 0x00 0x0231 0x0017 0x0000.01393dcf 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114831
0x17 9 0x00 0x0231 0x0013 0x0000.01393e33 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114833
0x18 9 0x00 0x0231 0x001a 0x0000.01393f48 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114838
0x19 9 0x00 0x0231 0x0018 0x0000.01393f03 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114838
0x1a 9 0x00 0x0231 0x001b 0x0000.01393fa7 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114838
0x1b 9 0x00 0x0231 0x001c 0x0000.01393ffd 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114839
0x1c 9 0x00 0x0231 0x0020 0x0000.01394045 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114840
0x1d 9 0x00 0x0230 0x0021 0x0000.0139363f 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114806
0x1e 9 0x00 0x0230 0x0001 0x0000.013936eb 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114810
0x1f 9 0x00 0x0230 0x001e 0x0000.013936b3 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114810
0x20 9 0x00 0x0231 0xffff 0x0000.0139408d 0x01c0021f 0x0000.000.00000000 0x00000001 0x00000000 1445114840
0x21 9 0x00 0x0230 0x001f 0x0000.0139367b 0x01c0021e 0x0000.000.00000000 0x00000001 0x00000000 1445114810
个人简介
8年oracle从业经验,具备丰富的oracle技能,目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户:
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式:
手机:18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博:wisdomone9
qq群:275813900
itpub博客名称:wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1815114/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1815114/