深入剖析一个完整的事务

oracle029

---

深入剖析一个完整的事务

2、图解一个事务的操作流程
Undo段的组成：段头、回滚块
事务ID：oracle为每个事务分配事务id

select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;//lists the active transactions in the system.
null null null null null null

insert into t values(1,'zhangsan');
select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;
070017008F020000 7 23 655 1037 3//当前活动事务

COMMIT

select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;

null null null null null null//说明事务已经被提交了

事务表
undo段的第一个数据块，每一个回滚段最多可以47个事务

回滚段的段头块
select * from v$rollname;

0 SYSTEM
1 _SYSSMU1_3780397527$
2 _SYSSMU2_2232571081$
3 _SYSSMU3_2097677531$
4 _SYSSMU4_1152005954$
5 _SYSSMU5_1527469038$
6 _SYSSMU6_2443381498$
7 _SYSSMU7_3286610060$
8 _SYSSMU8_2012382730$
9 _SYSSMU9_1424341975$
10 _SYSSMU10_3550978943$

select header_block,header_file from dba_segments where segment_name='_SYSSMU1_3780397527$';

128 3

alter system dump undo header '_SYSSMU1_3780397527$';//转储回滚段头

查看当前会话的进程编号
select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1));

spid//server process ID

5179

alter system dump undo header '_SYSSMU1_3780397527$';//转储回滚段

alter system dump datafile 5 block 4308;//转储回滚段数据块

1. 一个事务的开始要在undo的段头块的事务表中写事务信息XID，而且还要在将要被修改的数据块的事务槽中

写事务信息XID。

事务槽：xid（需要修改的块的事务槽）、uba（是修改原数据存在于undo中的数据块的地址信息）

深入剖析事务槽和事务的提交方式

7、事务槽ITL
默认是1
最大255（从Oracle10g开始不能更改）

• DBA_TABLESdescribes all relational tables in the database.

• USER_TABLESdescribes the relational tables owned by the current user. This view does not display theOWNERcolumn.

SQL> select INI_TRANS,MAX_TRANS from dba_tables where table_name='T';

INI_TRANS MAX_TRANS
---------- ----------
1 255

事务槽争用
create table t(id number(5),name char(2000));
insert into t values(1,'aa');
insert into t values(2,'bb');
insert into t values(3,'bb');
insert into t values(4,'cc');
insert into t values(5,'dd');
commit;
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),id from t;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID), ID

1 86193 1//某条记录所对应的文件编号和块号
1 86193 1
1 86193 2
1 86193 3
1 86193 4
1 86193 5

update t10 set name='abcd' where id=1;
select ubafil,ubablk,xidusn,xidslot,xidsqn,start_scnb from v$transaction;

UBAFIL, UBABLK, XIDUSN, XIDSLOT, XIDSQN, START_SCNB
3 11254919 868 1234672
//UBA的文件号，块号，回滚块的段头块，表中的哪一行记录，记录被覆盖第几次（这些信息既存在事务表中也存在事务操作中）

select * from v$rollname ;//查询段头块

0 SYSTEM
1 _SYSSMU1_3780397527$
2 _SYSSMU2_2232571081$
3 _SYSSMU3_2097677531$
4 _SYSSMU4_1152005954$
5 _SYSSMU5_1527469038$
6 _SYSSMU6_2443381498$
7 _SYSSMU7_3286610060$
8 _SYSSMU8_2012382730$
9 _SYSSMU9_1424341975$
10 _SYSSMU10_3550978943$

SQL> select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1)); 2

SPID
------------------------
11175//通过server Pro来到udump中查找对应的文件

alter system dump undo header '_SYSSMU8_2012382730$';

SQL> show parameter dump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/jia
gulun/jiagulun/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/jia
gulun/jiagulun/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/jia
gulun/jiagulun/trace
SQL>

[oracle@localhost trace]$ cat jiagulun_ora_11175.trc
Trace file /u01/app/oracle/diag/rdbms/jiagulun/jiagulun/trace/jiagulun_ora_11175.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/product/11.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine: x86_64
Instance name: jiagulun
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 11175, image: [e-mail] (TNS V1-V3)


*** 2015-01-03 00:05:56.449
*** SESSION ID:(36.245) 2015-01-03 00:05:56.449
*** CLIENT ID:() 2015-01-03 00:05:56.449
*** SERVICE NAME:(SYS$USERS) 2015-01-03 00:05:56.449
*** MODULE NAME:([e-mail] (TNS V1-V3)) 2015-01-03 00:05:56.449
*** ACTION NAME:() 2015-01-03 00:05:56.449


********************************************************************************
Undo Segment: _SYSSMU8_2012382730$ (8)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c000fc ext#: 1 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 1
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c000f1 length: 7
0x00c000f8 length: 8
0x00c02c00 length: 128

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1420272055
Extent Number:1 Commit Time: 1420272055
Extent Number:2 Commit Time: 1420267116

TRN CTL:: seq: 0x00b3 chd: 0x001b ctl: 0x0020 inc: 0x00000000 nfb: 0x0002
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c000fc.00b3.09 scn: 0x0000.0013265c
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c000fc.00b3.0b ext: 0x1 spc: 0x1a54
uba: 0x00c000fa.00b3.33 ext: 0x1 spc: 0x680
uba: 0x00000000.00b1.2d ext: 0x2 spc: 0x11bc
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0355 0x0016 0x0000.00132a0f 0x00c000f6 0x0000.000.00000000 0x00000001 0x00000000 1420272011
.... . ... .... ... ...... .... .... .... ... ....
0x20 9 0x00 0x0355 0xffff 0x0000.00132b09 0x00c000fc 0x0000.000.00000000 0x00000001 0x00000000 1420272233
0x21 9 0x00 0x0354 0x000a 0x0000.00132900 0x00c000f6 0x0000.000.00000000 0x00000001 0x00000000 1420271514
EXT TRN CTL::
usn: 8
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x7fff00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
... ..... ..... .... ...
0x21 0x00000000 0x00000000 0x00000000 0x00000000
[oracle@localhost trace]$

alter system dump datafile 3 block11254; // 回滚块，数据块