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_TABLES describes all relational tables in the database.
USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column. 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 11254 9 19 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/c |
|