深入剖析一個完整的事務，深入剖析完整事務

深入剖析一個完整的事務，深入剖析完整事務

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//當前活動事務 COMMITselect 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 ID5179alter 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), ID1     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/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: oracle@localhost.localdomain (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:(sqlplus@localhost.localdomain (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 block 11254; // 復原塊，資料區塊