<p> 分类： <span>Oracle</span> </p> <p> 原文地址：<span><a href="http://blog.chinaunix.net/uid-28460966-id-3662667.html">明明白白使用数据块 ----数据块格式深入解析</a></span> 作者：<span><a href="http://blog.chinaunix.net/uid/28460966.html">guoyJoe</a></span> </p> <p> <br /> </p> <div class="Blog_wz1" style="-ms-word-wrap:break-word;"> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Data Block是数据库中最小的I/O单元，下面我来简单介绍下数据块的基本结构。 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> OK！跟着我一步步实验： </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 一、建表空间 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>create tablespace tp1 datafile '/oradata/bxocp/tp01.dbf' size 10M; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 二、建用户及授权 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>create user gyj identified by gyj default tablespace tp1; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>grant dba to gyj; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 三、建表 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>conn gyj/gyj </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>create table t1 (id int,name varchar2(100)); </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 四、插入一行数据 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>insert into t1 values(1,'AAAAA'); </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>commit; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 五、手动发生一个检查点，使上面一行数据写到数据文件 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> alter system checkpoint; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 六、查这行数据所在的文件号和块号 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>col name for a10 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL>select  id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">         ID       NAME        FILE#     BLOCK# </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">        ----------   ---------       ----------    ---------- </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">          1      AAAAA        6        135 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 七、转储6号文件135号块，新开个窗口 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> [oracle@guoyj ~]$ sqlplus / as sysdba </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL> alter system dump datafile 6 block 135; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 八、找到转储的文件 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL> show parameter dump </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> NAME                                 TYPE        VALUE </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> ------------------------------------ ----------- ------------------------------ </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> background_core_dump                 string      partial </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> background_dump_dest                 string      /u01/app/oracle/diag/rdbms/bxo </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">                                                  cp/bxocp/trace </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 再开一新窗口 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> [oracle@guoyj ~]$ cd  /u01/app/oracle/diag/rdbms/bxocp/bxocp/trace </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> [oracle@guoyj trace]$ ls -lFtr </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 下面这个跟踪日志就是6号文件135号块转储出来的数据块信息 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> -rw-r----- 1 oracle oinstall   3363 Dec 11 18:02 bxocp_ora_5429.trc - </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 九、分析数据块结构 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> [oracle@guoyj trace]$ vi bxocp_ora_5429.trc </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Trace file /u01/app/oracle/diag/rdbms/bxocp/bxocp/trace/bxocp_ora_5429.trc </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> With the Partitioning, OLAP, Data Mining and Real Application Testing options </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> ORACLE_HOME = /u01/app/oracle/product/11.2.0 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> System name:    Linux </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Node name:      guoyj </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Release:        2.6.18-128.el5 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Version:        #1 SMP Wed Dec 17 11:41:38 EST 2008 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Machine:        x86_64 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> VM name:        VMWare Version: 6 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Instance name: bxocp </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Redo thread mounted by this instance: 1 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Oracle process number: 28 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Unix process pid: 5429, image: oracle@guoyj (TNS V1-V3) </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** SESSION ID:(29.15) 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** CLIENT ID:() 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** SERVICE NAME:(SYS$USERS) 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** MODULE NAME:(sqlplus@guoyj (TNS V1-V3)) 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> *** ACTION NAME:() 2012-12-11 18:02:31.307 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Start dump data blocks tsn: 7 file#:6 minblk 135 maxblk 135 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Block dump from cache: </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Dump of buffer cache at level 4 for tsn=7 rdba=25165959 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> BH (0x7f3f6958) file#: 6 rdba: 0x01800087 (6/135) class: 1 ba: 0x7f33a000 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 103,28 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   dbwrid: 0 obj: 76987 objn: 76987 tsn: 7 afn: 6 hint: f </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   hash: [0x908b5100,0x908b5100] lru: [0x7f3f6910,0x7f3f6b70] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   ckptq: [NULL] fileq: [NULL] objq: [0x7f3f6938,0x8d148e00] objaq: [0x7f3f6948,0x8d148df0] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 3 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   flags: block_written_once redo_since_read </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">   LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Block dump from disk: </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Arial, Helvetica, sans-serif;white-space:normal;background-color:#FFFFFF;"> 1、数据块头部分 </div> <table class="t_table xhe-border" style="border:1px solid #E3EDF5;border-image:none;width:993px;color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;table-layout:auto;empty-cells:show;background-color:#FFFFFF;" cellspacing="0"> <tbody> <tr> <td width="396" style="padding:4px;border:1px solid #E3EDF5;border-image:none;overflow:hidden;font-family:Arial, Helvetica, sans-serif;font-size:14px;"> <div align="right"> <div align="left"> buffer tsn: 7 rdba: 0x01800087 (6/135) </div> <div align="left"> scn: 0x0000.0015a3eb seq: 0x01 flg: 0x06 tail: 0xa3eb0601frmt: 0x02 chkval: 0xec19 type: 0x06=trans data </div> </div> </td> </tr> </tbody> </table> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> flg：0x01 (新建块)0x2(数据块延迟清洗推进scn和seq) 0X04(设置校验和) 0x08(临时块) </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> type：0x06(表/索引块)<br /> </div> <div align="left" style="line-height:21px;font-family:Arial, Helvetica, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> frmt:  0x01(v7)  0x02(v8) </div> <div align="left" style="line-height:21px;font-family:Arial, Helvetica, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Hex dump of block: st=0, typ_found=1 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Dump of memory from 0x00002B70E9566A00 to 0x00002B70E9568A00 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A00 0000A206 01800087 0015A3EB 06010000  [................] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A10 0000EC19 00000001 00012CBB 0015A3EA  [.........,......] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A20 00000000 0032F802 01800080 000F0004  [......2.........] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A30 00000346 00C00793 002200BB 00002001  [F.........".. ..] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A40 0015A3EB 00000000 00000000 00000000  [................] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A50 00000000 00000000 00000000 00000000  [................] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A60 00000000 00010100 0014FFFF 1F781F8C  [..............x.] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A70 00001F78 1F8C0001 00000000 00000000  [x...............] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E9566A80 00000000 00000000 00000000 00000000  [................] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">         Repeat 502 times </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 2B70E95689F0 0202012C 410502C1 41414141 A3EB0601  [,......AAAAA....] </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 2.ITL </div> <table class="t_table xhe-border" style="border:1px solid #E3EDF5;border-image:none;width:993px;color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;table-layout:auto;empty-cells:show;background-color:#FFFFFF;" cellspacing="0"> <tbody> <tr> <td width="513" style="padding:4px;border:1px solid #E3EDF5;border-image:none;overflow:hidden;font-family:Arial, Helvetica, sans-serif;font-size:14px;"> <div align="right"> <div align="left"> Block header dump:  0x01800087 </div> <div align="left"> Object id on Block? Y </div> <div align="left"> seg/obj: 0x12cbb  csc: 0x00.15a3ea  itc: 2  flg: E  typ: 1 - DATA </div> <div align="left">      brn: 0  bdba: 0x1800080 ver: 0x01 opc: 0 </div> <div align="left">      inc: 0  exflg: 0 </div> </div> </td> </tr> </tbody> </table> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> seg/obj: 0x12cbb --16进制转成10进制76987 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SQL> select object_id from dba_objects where object_name='T1' and owner='GYJ'; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> OBJECT_ID </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> ---------- </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">      76987 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> csc: 0x00.15a3ea --cleanoutSCN,块清除时的SCN </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> itc: 2   --ITL槽的数量 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> flg: E   --指用的是ASSM，如果是O表示用的是free list </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> typ: 1 - DATA   --事务型的数据块（并且：数据块头的type：0x06），存放表和索引数据。 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <table class="t_table xhe-border" style="border:1px solid #E3EDF5;border-image:none;width:973px;color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;table-layout:auto;empty-cells:show;background-color:#FFFFFF;" cellspacing="0"> <tbody> <tr> <td width="589" style="padding:4px;border:1px solid #E3EDF5;border-image:none;overflow:hidden;font-family:Arial, Helvetica, sans-serif;font-size:14px;"> <div align="right"> <div align="left"> Itl           Xid            Uba          Flag  Lck        Scn/Fsc </div> <div align="left"> 0x01  0x0004.00f.00000346 0x00c00793.00bb.22 --U-    1   fsc 0x0000.0015a3eb </div> <div align="left"> 0x02  0x0000.000.00000000 0x00000000.0000.00 ----    0   fsc 0x0000.00000000 </div> </div> </td> </tr> </tbody> </table> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Itl: ITL事务槽号的流水编号 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Xid：transac[X]tion identified(事务ID),由und的段号+undo的槽号+undo槽号的覆盖次数三部分组成 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Uba：undo block address记录了最近一次的该记录的前镜像（修改前的值） </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Flag：C是提交，U是快速提交，---是未提交 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Lck：锁住了几行数据，对应有几个行锁 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> Scn/Fsc：Scn=SCN of commited TX; Fsc=Free space credit(bytes) </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 这里fsc 0x0000.0015a3eb是指提交的scn，这个值大于上次清除块时的scn=csc: 0x00.15a3ea(此scn是这个块中最小的SCN of commited) </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> SCN WRAP:如果事务已提交并完成清洗，该字段保存事务提交SCN的SCN WRAP部分，否则该字段保存空闲预支字节数(FSC).比如我删除了一行数据10个字节，在事务提前前，这10个字节就属于fsc（即会写到SCN WRAP），只有事务提交后，才能正式返回到空闲空间。 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 3.用户数据头 </div> <table class="t_table xhe-border" style="border:1px solid #E3EDF5;border-image:none;width:973px;color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;table-layout:auto;empty-cells:show;background-color:#FFFFFF;" cellspacing="0"> <tbody> <tr> <td width="612" style="padding:4px;border:1px solid #E3EDF5;border-image:none;overflow:hidden;font-family:Arial, Helvetica, sans-serif;font-size:14px;"> <div align="right"> <div align="left">    bdba: 0x01800087 </div> <div align="left"> data_block_dump,data header at 0x2b70e9566a64 </div> <div align="left"> =============== </div> <div align="left"> tsiz: 0x1f98 </div> <div align="left"> hsiz: 0x14 </div> <div align="left"> pbl: 0x2b70e9566a64 </div> <div align="left">      76543210 </div> <div align="left"> flag=-------- </div> <div align="left"> ntab=1 </div> <div align="left"> nrow=1 </div> <div align="left"> frre=-1 </div> <div align="left"> fsbo=0x14 </div> <div align="left"> fseo=0x1f8c </div> <div align="left"> avsp=0x1f78 </div> <div align="left"> tosp=0x1f78 </div> <div align="left"> 0xe:pti[0]      nrow=1  offs=0 </div> <div align="left"> 0x12:pri[0]     offs=0x1f8c </div> </div> </td> </tr> </tbody> </table> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> bdba: 0x01800087  -- 数据块的地址：16进制转成2进制取前10位二进制为文件号0000 0001 1000 .....  0000000110=5号文件，后面剩于的部分表示块号，0X87转成10进制为135号块 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> tsiz: 0x1f98  --top of size 块的总大小即8088个字节 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> hsiz: 0x14   --Data header size 数据头大小即20个字节 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> pbl: 0x2b70e9566a64 --Pointer to buffer holding the block </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">       76543210 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> flag=--------  N=pcrfree hit(clusters);F=do not put on free list;K=flushable cluster keys </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> ntab=1     --叫表数：表示这个块的数据在一个表(如果是聚簇表就有可能是2或2以上) </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> nrow=1     --叫行数：表示这个表有一行数据 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> frre=-1      -- The first free row entry in the row directory＝you have to add one </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> fsbo=0x14   -- Free space begin offset  叫起始空间：可以存放数据空间的起始位置（即定义了数据层中空闲空间的起始offset） </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> fseo=0x1f8c  -- Free space end offset  叫结束空间：可以存放数据空间的结束位置（即定义了数据层中空闲空间的结束offset） </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> avsp=0x1f78  --Available space for new entries  叫空闲空间：定义了数据层中空闲空间的字节数 </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> tosp=0x1f78  --Total space   叫最终空闲空间：定义了ITL中事务提交后，数据层中空闲空间的字节数 </div> <div align="left" style="line-height:21px;font-family:Arial, Helvetica, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 0xe:pti[0]      nrow=1  offs=0  --Table directory,整个表的开始，共一行数据 ，定义了该表在行索引中使用的插槽数 </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 0x12:pri[0]     offs=0x1f8c      --Row index，叫行索引，定义了该块中包含的所有行数据的位置 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> 4.用户数据 </div> <table class="t_table xhe-border" style="border:1px solid #E3EDF5;border-image:none;width:973px;color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;table-layout:auto;empty-cells:show;background-color:#FFFFFF;" cellspacing="0"> <tbody> <tr> <td width="597" style="padding:4px;border:1px solid #E3EDF5;border-image:none;overflow:hidden;font-family:Arial, Helvetica, sans-serif;font-size:14px;"> <div align="right"> <div align="left">    block_row_dump: </div> <div align="left"> tab 0, row 0, @0x1f8c </div> <div align="left"> tl: 12 fb: --H-FL-- lb: 0x1  cc: 2 </div> <div align="left"> col  0: [ 2]  c1 02 </div> <div align="left"> col  1: [ 5]  41 41 41 41 41 </div> <div align="left"> end_of_block_dump </div> <div align="left"> End dump data blocks tsn: 7 file#: 6 minblk 135 maxblk 135 </div> </div> </td> </tr> </tbody> </table> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> tab 0, row 0, @0x1f8c      --第一个表第一行的位置，定义了该表在行索引中的起始插槽号 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> tl: 12 fb: --H-FL-- lb: 0x1  cc: 2  --行头，tl: 12行长度12个字节， </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> fb: (Flag byte)--H-FL指H（Head piece of row）F(First data piece) L(Last data piece) </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> lb: 0x1 --Lock byte和上面的ITL的lck相对应，表示这行是否被lock了 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> cc: 2 --表示有两列，即这个表有两个字段 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> col  0: [ 2]  c1 02 --第一行的第一个字段长度和值 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> col  1: [ 5]  41 41 41 41 41 --第一行的第二个字段长度和值 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 数据块的最后四字节tail: 0xa3eb0601＝scnBASE+flg+seq,如果不相等会报块损坏！！！<br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;white-space:normal;background-color:#FFFFFF;"> </div> <p align="left" style="color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;margin-top:0px;margin-bottom:0px;white-space:normal;background-color:#FFFFFF;"> 5、下面对这些数据用SQL语句做相互转换 </p> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> （1）把表中的数据转成16进制（即在ORACLE内部数据块看到的数据），用以下sql语句： </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p>   gyj@OCM> select id,name,dump(01,'16'), dump('AAAAA','16')  from t1;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />   ID NAME       DUMP(01,'16')     DUMP('AAAAA','16')<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> ---- ---------- ----------------- ----------------------------<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />    1 AAAAA      Typ=2 Len=2: c1,2 Typ=96 Len=5: 41,41,41,41,41<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> （2）反过来把16进制转成表中的数据（当然我这边的数据类型只考虑了number和varchar类型），用以sql下语句：<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> ggyj@OCM> col id for 999gyj@OCM> col id1 for 999<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> gyj@OCM> col name for a10<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> gyj@OCM> col name1 for a10<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> gyj@OCM> select id,UTL_RAW.CAST_TO_NUMBER(replace(' c1 02 ',' ')) id1,<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />   2  name,UTL_RAW.CAST_TO_VARCHAR2(replace('41 41 41 41 41',' ')) name1<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />   3  from t1;<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> <br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />   ID  ID1 NAME       NAME1<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> ---- ---- ---------- ----------<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" />    1    1 AAAAA      AAAAA<br style="margin:0px;padding:0px;color:#444444;font-family:Tahoma, "Microsoft Yahei", Simsun;white-space:normal;background-color:#FFFFFF;" /> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> <br /> </p> <p style="line-height:normal;padding-top:2px;padding-bottom:2px;font-family:Arial, Helvetica, sans-serif;margin-top:4px;margin-bottom:4px;white-space:normal;background-color:#FFFFFF;"> **************************************************************************************************************************************************************************** </p> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 一、对于我上面讲的： 1、数据块头部分中讲的flg标志再做一些详细的补充 </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 0x01   (新建块)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x2   (数据块延迟清洗推进scn和seq) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0X04  (设置校验和) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x08  (临时块)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 对于 flg标志的值是由上面一些位做组合运算的，下面我们来看几下例子：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 1、先来看看0x01   (新建块)和0X04  (设置校验和) 的组合<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 我的版本是11gr2<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> select * from v$version;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> BANNER<br style="margin:0px;padding:0px;" /> --------------------------------------------------------------------------------<br style="margin:0px;padding:0px;" /> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 由于11gr2在创建表的时侯有个延迟段参数的控制，默认不分配EXTNETS空间<br style="margin:0px;padding:0px;" /> gyj@OCM> show show parameter deferred_segment_creation<br style="margin:0px;padding:0px;" /> showmode OFF<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> NAME                                 TYPE        VALUE<br style="margin:0px;padding:0px;" /> ------------------------------------ ----------- ------------------------------<br style="margin:0px;padding:0px;" /> deferred_segment_creation            boolean     TRUE<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 那么我在建表的同时要立马分配EXTENTS,好我马上建个表，操作如下：<br style="margin:0px;padding:0px;" /> gyj@OCM> create table t20(id int ,name varchar2(10)) SEGMENT CREATION IMMEDIATE;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Table created.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 查分配的区号，文件号，块号<br style="margin:0px;padding:0px;" /> gyj@OCM> select extent_id,file_id,block_id from dba_extents where segment_name='T20';<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> EXTENT_ID    FILE_ID   BLOCK_ID<br style="margin:0px;padding:0px;" /> ---------- ---------- ----------<br style="margin:0px;padding:0px;" />          0          3        152<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 对3号文件的152号块做DUMP,做dump时最好新打会一个会话，避免产生不必要的日志，操作如下<br style="margin:0px;padding:0px;" /> [oracle@ocm ~]$ sqlplus / as sysdba<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 18 07:40:55 2013<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Copyright (c) 1982, 2009, Oracle.  All rights reserved.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Connected to:<br style="margin:0px;padding:0px;" /> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production<br style="margin:0px;padding:0px;" /> With the Partitioning, OLAP, Data Mining and Real Application Testing options<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> sys@OCM> alter system dump datafile 3 block 152;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 好，现在马上到找到跟踪日志<br style="margin:0px;padding:0px;" /> sys@OCM> show parameter dump<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> NAME                                 TYPE        VALUE<br style="margin:0px;padding:0px;" /> ------------------------------------ ----------- ------------------------------<br style="margin:0px;padding:0px;" /> background_core_dump                 string      partial<br style="margin:0px;padding:0px;" /> background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ocm<br style="margin:0px;padding:0px;" />                                                  /ocm/trace<br style="margin:0px;padding:0px;" /> core_dump_dest                       string      /u01/app/oracle/diag/rdbms/ocm<br style="margin:0px;padding:0px;" />                                                  /ocm/cdump<br style="margin:0px;padding:0px;" /> max_dump_file_size                   string      unlimited<br style="margin:0px;padding:0px;" /> shadow_core_dump                     string      partial<br style="margin:0px;padding:0px;" /> user_dump_dest                       string      /u01/app/oracle/diag/rdbms/ocm<br style="margin:0px;padding:0px;" />                                                  /ocm/trace<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> [oracle@ocm trace]$ cd /u01/app/oracle/diag/rdbms/ocm/ocm/trace<br style="margin:0px;padding:0px;" /> [oracle@ocm trace]$ ls -lFtr<br style="margin:0px;padding:0px;" /> [oracle@ocm trace]$ more ocm_ora_12910.trc<br style="margin:0px;padding:0px;" /> 贴出数据块头的信息：<br style="margin:0px;padding:0px;" /> scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001<br style="margin:0px;padding:0px;" /> frmt: 0x02 chkval: 0xa798 type: 0x00=unknown<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 从上面看出flg是flg: 0x05，就是0x01 +0x04的组合,<br style="margin:0px;padding:0px;" /> 0x01说明这是一个新建的块，因为我的表的是刚刚创建的，没有向块中插入数据。<br style="margin:0px;padding:0px;" /> 0x04说明有设置校验，chkval: 0xa798这个就是校验和，这个与参数db_block_checksum有关<br style="margin:0px;padding:0px;" /> gyj@OCM> show parameter db_block_checksum<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> NAME                                 TYPE        VALUE<br style="margin:0px;padding:0px;" /> ------------------------------------ ----------- ------------------------------<br style="margin:0px;padding:0px;" /> db_block_checksum                    string      TYPICAL<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 默认设置了db_block_checksum，就会启动校验和检查数据块的一致性。另外数据块尾部的4个字节也是配合数据块头来验证数据块的一致性。<br style="margin:0px;padding:0px;" /> 如果一个块头被标识成软损坏，那么块头的序列号为0xff,标志为0x00<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 2、再来看0x2   (数据块延迟清洗推进scn和seq) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 对于延迟块清洗推进要细说有点复杂，到时候对这一块开个专题，这里我就简单介绍一下。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 在11g版本中，对数据DML操作，Oracle都是采取快速提交，在事务槽中可以观察到,OK,我先来做个测试来验证一下：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 先插入一条数，然后提交，再dump，操作如下：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> insert into t1 values(2,'BBBBB');<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 1 row created.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> commit;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Commit complete.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> alter system flush buffer_cache;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> gyj@OCM> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t1 where id=2;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" />   ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)<br style="margin:0px;padding:0px;" /> ---- ---------- ------------------------------------ ------------------------------------<br style="margin:0px;padding:0px;" />    2 BBBBB                                         3                                  132<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> sys@OCM> alter system dump datafile 3 block 132;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> dump 的内容如下：注意看下面的红色字体部分<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">数据块头</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> buffer tsn: 7 rdba: 0x00c00084 (3/132) </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> scn: 0x0000.004bb8d3 seq: 0x02 flg: 0x06 tail:0xb8d30602 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> frmt: 0x02 chkval: 0x6a0c type:0x06=trans data </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">事务ITL</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> Object id on Block? Y </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> seg/obj: 0x12723 csc: 0x00.4bb8d1  itc: 2 flg: E  typ: 1 - DATA </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      brn: 0  bdba:0xc00080 ver: 0x01 opc: 0 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      inc: 0 exflg: 0 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> Itl          Xid                  Uba        Flag  Lck        Scn/Fsc </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x01   0x0008.016.00001131 0x018019a3.0160.16  --U-    1  fsc 0x0000.004bb8d3 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x02   0x0003.013.0000107a 0x018017c7.0128.23  C---    0  scn 0x0000.004bb60d </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">数据部分</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> bdba: 0x00c00084 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> data_block_dump,data header at0x827664 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> =============== </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tsiz: 0x1f98 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> hsiz: 0x18 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> pbl: 0x00827664 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      76543210 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> flag=-------- </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> ntab=1 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> nrow=3 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> frre=-1 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> fsbo=0x18 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> fseo=0x1f75 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> avsp=0x1f5d </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tosp=0x1f5d </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0xe:pti[0]      nrow=3 offs=0 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x12:pri[0]     offs=0x1f8c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x14:pri[1]     offs=0x1f81 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x16:pri[2]     offs=0x1f75 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> block_row_dump: </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 0, @0x1f8c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 12 fb: --H-FL-- lb: 0x0  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 02 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 5]  41 41 41 41 41 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 1, @0x1f81 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 11 fb: --H-FL-- lb: 0x0  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 02 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 4]  67 79 6a 31 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 2, @0x1f75 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 12 fb: --H-FL-- lb: 0x1  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 03 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 5]  42 42 42 42 42 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> end_of_block_dump </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> 从上面的<span style="margin:0px;padding:0px;">事务ITL上</span>看到0x01 0x0008.016.00001131 0x018019a3.0160.16 --U- 1 fsc 0x0000.004bb8d3，这里提交标记是U,表示快示提交，它对应的就是我们的刚刚插入的那行记录。<br style="margin:0px;padding:0px;" /> 即<br style="margin:0px;padding:0px;" /> gyj@OCM> insert into t1 values(2,'BBBBB');<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 1 row created.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> commit;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 实际上很容易看出，从上面的数据看出<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tab 0, row 2, @0x1f75<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tl: 12 fb: --H-FL-- lb: 0x1 cc: 2          ---lb: 0x1 对应的就是事务槽的1号槽。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col 0: [ 2] c1 03<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col 1: [ 5] 42 42 42 42 42              ----16进制42就是B<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 那么什么时候会把提交标记U变成C呢，当我再修改另一行记录时，Oracle向 0x02 0x0003.013.0000107a 0x018017c7.0128.23 C--- 0 scn 0x0000.004bb60d  2号事务槽插入事务，2号事槽的提交标记又变成快速提交，同时oracle会把原来1号事务槽的提交标记由U（快速提交）变成C（正常提交），并且同时还清除锁标记Lck ＝0  and lb: 0x00,最后就是我们要说的数据块延迟清洗推进 ，即在csc: 0x00.4bb8d1 改修最后的SCN,实际上csc 是本块的最小的commit SCN.<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 那下面我按上面所说再来做个操作，即插入一下数据，提交，dump，再观察块头的 flg,事务槽,数据,及 csc。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> update t1 set name='CCCCC' where id=1;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 2 rows updated.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> commit;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Commit complete.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> alter system flush buffer_cache;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> sys@OCM> alter system dump datafile 3 block 132;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> dump 的内容如下：注意看下面的红色字体部分<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">数据块头</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> buffer tsn: 7 rdba: 0x00c00084 (3/132) </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> scn: 0x0000.004bbba3 seq: 0x03 flg: 0x06 tail:0xbba30603 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> frmt: 0x02 chkval: 0x3d04 type:0x06=trans data </p> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">事务ITL</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> Block header dump:  0x00c00084 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> Object id on Block? Y </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> seg/obj: 0x12723  csc:0x00.4bb8d1  itc: 2  flg: E  typ: 1 - DATA </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      brn: 0  bdba:0xc00080 ver: 0x01 opc: 0 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      inc: 0 exflg: 0 </p> <br style="margin:0px;padding:0px;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> Itl          Xid                  Uba        Flag  Lck        Scn/Fsc </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x01   0x0008.016.00001131 0x018019a3.0160.16  --U-    1  fsc 0x0000.004bb8d3 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x02   0x0004.003.00000c54 0x01801a36.00d1.03  --U-    2  fsc 0x0000.004bbba3 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <br /> </p> <br style="margin:0px;padding:0px;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">数据部分</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> bdba: 0x00c00084 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> data_block_dump,data header at0xc03664 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> =============== </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tsiz: 0x1f98 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> hsiz: 0x18 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> pbl: 0x00c03664 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;">      76543210 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> flag=-------- </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> ntab=1 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> nrow=3 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> frre=-1 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> fsbo=0x18 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> fseo=0x1f69 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> avsp=0x1f5c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tosp=0x1f5c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0xe:pti[0]      nrow=3 offs=0 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x12:pri[0]     offs=0x1f8c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x14:pri[1]     offs=0x1f69 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> 0x16:pri[2]     offs=0x1f75 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> block_row_dump: </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 0, @0x1f8c </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 12 fb: --H-FL-- lb: 0x2  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 02 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 5]  43 43 43 43 43 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 1, @0x1f69 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 12 fb: --H-FL-- lb: 0x2  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 02 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 5]  43 43 43 43 43 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tab 0, row 2, @0x1f75 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> tl: 12 fb: --H-FL-- lb: 0x1  cc:2 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  0: [ 2]  c1 03 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> col  1: [ 5]  42 42 42 42 42 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> end_of_block_dump </p> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> dump出来发现不是我上面所说的，呵呵。。。，现在两个事务槽的提交标记都是U，U就是代表快速提交，即不清楚行锁。看下面两个dump的csc: 0x00.4bb8d1 即数据块延迟 清洗 没有推进，不要急，我们再做一个操作，再操作一下事务，这次肯定会清楚行锁，并且csc: 0x00.4bb8d1 即 数据块延迟  清洗 肯定会推进。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> insert into t1 values(3,'DDDDDD') ;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> 1 row created.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> commit;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Commit complete.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> gyj@OCM> alter system flush buffer_cache;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> sys@OCM> alter system dump datafile 3 block 132;<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> System altered.<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> dump 的内容如下：注意看下面的红色字体部分 <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <p align="left" style="margin-top:0px;margin-bottom:0px;"> <span style="margin:0px;padding:0px;">数据块头</span> </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> buffer tsn: 7 rdba: 0x00c00084 (3/132)<br style="margin:0px;padding:0px;" /> scn: 0x0000.004bbd8b seq: 0x01 flg: 0x06 tail: 0xbd8b0601<br style="margin:0px;padding:0px;" /> frmt: 0x02 chkval: 0xc1f4 type: 0x06=trans data<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <span style="margin:0px;padding:0px;">事务ITL</span> <br style="margin:0px;padding:0px;" /> Object id on Block? Y<br style="margin:0px;padding:0px;" /> seg/obj: 0x12723  csc: 0x00.4bbd8a  itc: 2  flg:E  typ: 1 - DATA<br style="margin:0px;padding:0px;" />      brn: 0  bdba: 0xc00080 ver: 0x01 opc: 0<br style="margin:0px;padding:0px;" />      inc: 0  exflg: 0<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> Itl           Xid                 Uba        Flag  Lck       Scn/Fsc<br style="margin:0px;padding:0px;" /> 0x01  0x0002.014.0000105d  0x01801aa2.0130.17  --U-   1  fsc 0x0000.004bbd8b<br style="margin:0px;padding:0px;" /> 0x02  0x0004.003.00000c54  0x01801a36.00d1.03  C---   0  scn 0x0000.004bbba3<br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> <span style="margin:0px;padding:0px;">数据部分</span> <br style="margin:0px;padding:0px;" /> bdba: 0x00c00084<br style="margin:0px;padding:0px;" /> data_block_dump,data header at 0xd85664<br style="margin:0px;padding:0px;" /> ===============<br style="margin:0px;padding:0px;" /> tsiz: 0x1f98<br style="margin:0px;padding:0px;" /> hsiz: 0x1a<br style="margin:0px;padding:0px;" /> pbl: 0x00d85664<br style="margin:0px;padding:0px;" />      76543210<br style="margin:0px;padding:0px;" /> flag=--------<br style="margin:0px;padding:0px;" /> ntab=1<br style="margin:0px;padding:0px;" /> nrow=4<br style="margin:0px;padding:0px;" /> frre=-1<br style="margin:0px;padding:0px;" /> fsbo=0x1a<br style="margin:0px;padding:0px;" /> fseo=0x1f5c<br style="margin:0px;padding:0px;" /> avsp=0x1f4d<br style="margin:0px;padding:0px;" /> tosp=0x1f4d<br style="margin:0px;padding:0px;" /> 0xe:pti[0]      nrow=4  offs=0<br style="margin:0px;padding:0px;" /> 0x12:pri[0]     offs=0x1f8c<br style="margin:0px;padding:0px;" /> 0x14:pri[1]     offs=0x1f69<br style="margin:0px;padding:0px;" /> 0x16:pri[2]     offs=0x1f75<br style="margin:0px;padding:0px;" /> 0x18:pri[3]     offs=0x1f5c<br style="margin:0px;padding:0px;" /> block_row_dump:<br style="margin:0px;padding:0px;" /> tab 0, row 0, @0x1f8c<br style="margin:0px;padding:0px;" /> tl: 12 fb: --H-FL-- lb: 0x0  cc: 2<br style="margin:0px;padding:0px;" /> col  0: [ 2]  c1 02<br style="margin:0px;padding:0px;" /> col  1: [ 5]  43 43 43 43 43<br style="margin:0px;padding:0px;" /> tab 0, row 1, @0x1f69<br style="margin:0px;padding:0px;" /> tl: 12 fb: --H-FL-- lb: 0x0  cc: 2<br style="margin:0px;padding:0px;" /> col  0: [ 2]  c1 02<br style="margin:0px;padding:0px;" /> col  1: [ 5]  43 43 43 43 43<br style="margin:0px;padding:0px;" /> tab 0, row 2, @0x1f75<br style="margin:0px;padding:0px;" /> tl: 12 fb: --H-FL-- lb: 0x0  cc: 2<br style="margin:0px;padding:0px;" /> col  0: [ 2]  c1 03<br style="margin:0px;padding:0px;" /> col  1: [ 5]  42 42 42 42 42<br style="margin:0px;padding:0px;" /> tab 0, row 3, @0x1f5c<br style="margin:0px;padding:0px;" /> tl: 13 fb: --H-FL-- lb: 0x1  cc: 2<br style="margin:0px;padding:0px;" /> col  0: [ 2]  c1 04<br style="margin:0px;padding:0px;" /> col  1: [ 6]  44 44 44 44 44 44 </p> <p align="left" style="margin-top:0px;margin-bottom:0px;"> end_of_block_dump </p> <br style="margin:0px;padding:0px;" /> <br style="margin:0px;padding:0px;" /> flg: 0x06 ＝0x02+0x04<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 果然是这样，当事务槽上的提交标志都是快速提交（U），那再有事务进来，Oracle先找个ITL SLOT插入事务，顺便把其它ITL slot上的快速提交U 变成正常提交C，并且清除行锁，最最最得要我想说的就是 csc: 0x00.4bbd8a 数据块延迟 清洗真的推进了(原来是csc: 0x00.4bb8d1)。。。 一定记住 csc: 0x00.4bbd8a 是数据本块中最小的COMMIT SCN，实际上它表示上次事务槽上没清除的锁现在清除一下，然后就做了一个延迟 清洗推进 .<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;">  好，即然说到这里，我再对数据块中非常重要的一块再单独拿出来讲讲：就是ITL（事务槽），事务在数据库中非常重要，如果要细讲事务，那东西很多。这里我先来简单讲讲事务。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />     那什么是事务？事务的定义是一个独立的逻辑工作单元：它由特定的一系列必须作为一个整体一起成功或失败的SQL语句组成。事务可以由多个数据操作语言(data manipulation language,DML)语句组成，但只能含有一个数据定义语言(data definition language,DDL)语句。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />     事务的ACID特征<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 　    A)、原子性(Atomicity) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />     　　事务中的所有动作要么都发生，要么都不发生<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 　    B)、一致性(Consistency) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />         事务将数据库从一种状态转变为下一种一致状态<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />       C)、隔离性(Isolation)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />          一个事务的影响在该事务提交前对其他事务都不可见<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 　    D)、持久性(Durability) <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />          事务一旦提交，其结果就是永久性的 <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    事务的定义和特性就说到这里，我继续dump，分本一下数据块中的ITL槽，OK，我现在马上开始一个事务：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    我现在把T1表中的id=3的这行的name=DDDDDD 改成EEEEEE，做UPDATE操作，不提交，让事务一直活动着。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> from t1 where id=3;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />         ID NAME            FILE#     BLOCK#<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ---------- ---------- ---------- ----------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />          3 DDDDDD              3        132<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> update t1 set name ='EEEEEE' where id=3;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 1 row updated.<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   这时先不要提交，让事务活动着，一会去观察块中的ITL槽，为了能让上面修改的数据马上写到数据文件，执行缓存刷新操作，<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 如下：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> gyj@OCM> alter system flush buffer_cache;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> System altered.<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   好，这时，我马上开一个新窗口做dump操作：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   sys@OCM> alter system dump datafile 3 block 132;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> System altered.<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    贴出DUMP的主要内容ITL部分：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Object id on Block? Y<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> seg/obj: 0x12723  csc: 0x00.4c5fd3  itc: 2  flg: E  typ: 1 - DATA<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />      brn: 0  bdba: 0xc00080 ver: 0x01 opc: 0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />      inc: 0  exflg: 0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Itl           Xid                  Uba         Flag  Lck        Scn/Fsc<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x01   0x0002.014.0000105d  0x01801aa2.0130.17  C---    0  scn 0x0000.004bbd8b<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x02   0x0008.00b.00001144  0x01801e95.0161.1b  ----    1  fsc 0x0000.00000000<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 这里 seg/obj: 0x12723就是对象号，我们用计算器把0x12723(16进制转化成10进制)得到75555，就是说对角号是75555.<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> sys@OCM> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_NAME='T1' and owner='GYJ';<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> OBJECT_ID DATA_OBJECT_ID<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ---------- --------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />      75350          75555<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 各位兄弟注意了，这里的对象号是指段的号即DATA_OBJECT_ID（ OBJECT_ID与DATA_OBJECT_ID，什么时候不一样呢，一般做truncate操作，DATA_OBJECT_ID就会发生变化，这里就不细说了）<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   好，我们的重点就是看ITL槽，从上面的ITL看出，我们刚刚操作的update正在活动的事务就是在第二个事务槽上即：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Itl           Xid                  Uba         Flag  Lck        Scn/Fsc<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x02   0x0008.00b.00001144  0x01801e95.0161.1b  ----    1  fsc 0x0000.00000000<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   我主要来分析一下Xid和Uba<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   首先Xid是由XIDUSN（Undo segment number）、XIDSLOT（Slot number）+XIDSQN（Sequence number）三部分组成的。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   OK，即然说到事务，我们不得不看视图:v$transaction:<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> sys@OCM> select xid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,status from v$transaction;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> XID                  XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 08000B0044110000          8         11       4420          6       7829        353         27 ACTIVE<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 从上面的视图v$transaction得到：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    XIDUSN＝8    (8号回滚段)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    XIDSLOT＝11  (在8号回滚段的事务表的第11行)，哥哥这里不要晕哦，这里的事务表是指8号回滚段的段头块。<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />    XIDSQN＝4420 (事务表第11行被覆盖了4420次)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 把上面数据块中ITL事务槽中的Xid＝0x0008.00b.00001144进行分解，你们看：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   0x0008 （16进制）－－> （10进制）8<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   00b  （16进制）－－>（10进制） 11<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   00001144（16进制）－－>（10进制）4420<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 分解出来完本与我们在transaction中看到的 XIDUSN    XIDSLOT     XIDSQN完成一样！<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 好，我们再来看ILT中的Uba＝0x01801e95.0161.1b进行分析：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Uba由文件号、块号、序列号及记录号四部分组成的：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x01801e95（16进制）－－> （10进制）由四个字节组成把它转成32位的二进制，取前面10位二进制得到0000000110＝6，剩下的<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 22位＝7829（其实就是0x1e95用计算器转得到7829）<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0161 （16进制）－－> （10进制） 353<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 1b    （16进制）－－> （10进制） 27<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 我靠，分解出来与我们在transaction中看到的UBAFIL     UBABLK     UBASQN     UBAREC完成一致！<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 兄弟们，你们觉得知道这些有什么用吗，其实很有用啊，这些就是UNDO啊，呵呵Oracle之前就靠UNDO打下半壁江山，对于UNDO，我<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 会在下后面的帖中做详细介绍让彻底解读UNDO让一致性读不再是秘密！（这里留个位置放undo的超链接）<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   讲到这里，其实还有个跟事务分不开的东东，那就是锁，一个很重要的视图：v$lock;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> sys@OCM> select * from v$lock where sid=183;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 50C84DA4 50C84DD0        183 AE        100          0          4          0      22492          0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 02066600 02066630        183 TM      75350          0          3          0       2893          0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 4FA268F4 4FA26934        183 TX     524299       4420          6          0       2893          0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 这信息很有用：<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> TM锁：TM-75350-0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> TX锁：TX-524299-4420<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 对于锁制机会在后面的帖中分享一下移动级的：锁等待分析处理、DX锁等待处理（这里留个位置放超链接）<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br /> 最后总结：数据块的组成部分，包括以下四部分： </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> 1、数据块头<br style="line-height:18px;font-family:verdana, arial, helvetica;background-color:#DFDFDF;" /> 2、事务槽ITL<br style="line-height:18px;font-family:verdana, arial, helvetica;background-color:#DFDFDF;" /> 3、数据<br style="line-height:18px;font-family:verdana, arial, helvetica;background-color:#DFDFDF;" /> 4、数据块尾 </div> <div align="left" style="line-height:normal;font-family:Arial, Helvetica, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <span style="line-height:21px;"><br style="margin:0px;padding:0px;" /> </span>数据块格式就先说到这里了，有问题的兄弟可以一起讨论学习！不断更新中。。。<br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br /> </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> ******************************************************************************************************<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 补充：NUMBER类型的转储<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col  0: [ 2]  c2 02 数字100转存后是这样<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select (to_number('2','xxxx')-1)*power(100,to_number('c2','xxxx')-193) from dual;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> (TO_NUMBER('2','XXXX')-1)*POWE<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ------------------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />                            100<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 这样就算回去了<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 数字-123.333 转存后是<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col  0: [ 6]  3d 64 59 59 47 66<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> select -((101 - to_number('64', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx')) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('59', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 1) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('59', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 2) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('47', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 3) )<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 这样就算回去了<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 转存后是16进制的<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 要改回10进制<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 创建一个表<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> create table t１<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   2  (it number);<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Table created<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> insert into t１<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   2  values(100);<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 1 row inserted<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 计算器块<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select rowid from t１;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ROWID<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> AAAXKYAABAAAU4aAAA<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select dbms_rowid.rowid_relative_fno('AAAXKYAABAAAU4aAAA'),dbms_rowid.rowid_block_number('AAAXKYAABAAAU4aAAA') from t１;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ------------------------------ ------------------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />                              1                          85530<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 进行DUMP<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> alter system dump datafile 1 block 85530;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> System altered<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 查看其数字的dump格式<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> flag=--------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ntab=1<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> nrow=1<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> frre=-1<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> fsbo=0x14<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> fseo=0x1f9a<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> avsp=0x1f83<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tosp=0x1f83<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0xe:pti[0]        nrow=1        offs=0<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 0x12:pri[0]        offs=0x1f9a<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> block_row_dump:<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tab 0, row 0, @0x1f9a<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tl: 6 fb: --H-FL-- lb: 0x1  cc: 1 <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col  0: [ 2]  c2 02  ----这里就是DUMP出来的，长度是2 <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> end_of_block_dump<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> End dump data blocks tsn: 0 file#: 1 minblk 85530 maxblk 85530<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 其实这里的C2 02就是其具体的数字。使用DUMP函数也能得出<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select dump(it,16) from t１;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> DUMP(IT,16)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> --------------------------------------------------------------------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Typ=2 Len=2: c2,2<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 然后通过ORACLE的算法计算回去<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select to_number('c2','xxxx') from dual;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> TO_NUMBER('C2','XXXX')<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ----------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />                    194<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select to_number('2','xxxx') from dual;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> TO_NUMBER('2','XXXX')<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ---------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />                     2<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 其实这里的如果换算为10进制是<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 194,2<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 如果大于128 就是正数，小于128就是负数<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 指数是194-193=1<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 数字位1 是2-1=1*100^(1-0)=100 -1是因为正数+1存储<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 所以数字也就还原为100<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select (to_number('2','xxxx')-1)*power(100,to_number('c2','xxxx')-193) from dual;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> (TO_NUMBER('2','XXXX')-1)*POWE<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> ------------------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />                            100<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 用SQL就是这样，这是第一位<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 在加入一个负数<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> insert into test<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   2  values(-123.333);<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 1 row inserted<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> commit;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Commit complete<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 进行DUMP如上<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tab 0, row 1, @0x1f90<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> tl: 10 fb: --H-FL-- lb: 0x2  cc: 1<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> col  0: [ 6]  3d 64 59 59 47 66<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 进行计算<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Typ=2 Len=6: 3d,64,59,59,47,66<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> SQL> select dump(it,16) from testpp;<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> DUMP(IT,16)<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> --------------------------------------------------------------------------------<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Typ=2 Len=2: c2,2<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> Typ=2 Len=6: 3d,64,59,59,47,66  --66 及10进制的102是一个排序位不用理会，用在负数的时候<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 内部存储长度6 指数3d 后面数数字位<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 换算为10进制就是<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 61，100，89，89，71，102<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 61是指数未<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 102 是排序位<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 中间的数字位<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> select -((101 - to_number('64', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx')) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('59', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 1) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('59', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 2) +<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        (101 - to_number('47', 'xxxx')) *<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />        power(100, 62 - to_number('3d', 'xxxx') - 3) )<br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" />   from dual; </div> <div align="left" style="color:#444444;line-height:21px;font-family:Tahoma, Helvetica, SimSun, sans-serif;font-size:14px;white-space:normal;background-color:#FFFFFF;"> <br style="margin:0px;padding:0px;line-height:18px;font-family:Tahoma, "Microsoft Yahei", Simsun;" /> 这样数字就完成了转换。<br /> <br /> <span style="color:#333333;">**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********</span><br /> QQ: 252803295<br /> Email:dbathink@hotmail.com<br /> 尖峰官网：<a href="http://www.jianfengedu.com/" target="_blank">http://www.jianfengedu.com</a><br /> 尖峰淘宝：<a href="http://www.jianfengedu.com/" target="_blank">http://jianfengedu.taobao.com</a><br /> WEIBO：<a href="http://www.jianfengedu.com/" target="_blank">http://weibo.com/guoyJoe0218</a><br /> 尖峰OCP认证考试群297227448  <br /> 尖峰OCM认证考试群99606943<br /> 尖峰MySQL研究院群314746420<br /> 尖峰JAVA研究院群 315405063  <br /> 尖峰Hadoop研究院群366294602<br /> 尖峰线上技术分享群252296815<br /> 尖峰SQL优化研究院群250057366<br /> </div> </div>

ITPub博客

明明白白使用数据块 ----数据块格式深入解析 2014

最新文章