(学用Oracle也已近三年时间了

ASimpleExperimentforOracleTransaction1.Preface(学用Oracle也已近三年时间了,证书也考下来了,但心中一直对Oracle底层的运作怀有无比的好奇心和疑惑.就比如SG上说更新数据前会先写undo,而undo的rolbacktransactiontable,rollbackdatablock发生变化时，又写redo,…等等。在此严谨的规则背后,Oracle究竟是如何实现的?这些实现和Oracle底层的各数据文件,控制文件,日志文件又是何关系.在出现异常时（比如Instancecrash后，Oracle做instancerecovery的rollforward/rollback），这时都使用到哪些文件中的哪些数据…,这些内容是复杂的,需要掌握很多Oracle低层原理,做很多的实验才能逐渐将它们清晰化,才能掌握它们,并在实际工作中运用它们来解决问题.就这篇文章而言,并不能够解决所有问题,因为本文只是update一张表的一条记录,然后再rollback的操作实验过程,很简单,因为只是开始.读此文章请注意:A.红颜色的字都是本人加入的注释或可称为废话,可能与需注释的信息在同一行或换新行,且都是用双#号封闭起来了.B.蓝颜色的字都与REDOLOG中的changevector相关.C.黑体字(文章及段落标题除外)都为偶所希望描述的或需特别注意的内容.D.背景为灰色部分全都是执行SQL后SQLPLUS的输出结果或DUMP文件中的内容(因部分DUMP文件特别大,故只取了与本实验相关的部分)且以上ABCD四种属性可能相互组合:).2.ExperimentProcessStep2.0connecttooracle2.0.1connectassysdbaconnect/assysdbaselect*fromv$version;BANNER----------------------------------------------------------Oracle9iEnterpriseEditionRelease9.2.0.6.0-ProductionPL/SQLRelease9.2.0.6.0-ProductionCORE9.2.0.6.0ProductionTNSfor32-bitWindows:Version9.2.0.6.0-ProductionNLSRTLVersion9.2.0.6.0–Production#在Linux下先做了第一遍,未能在一个工作日内将全部内容串起来,只能回来在Windows2000Professional上做了.#2.0.2querydba_tablespacestoshowtheircharacterselecttablespace_name,block_size,status,contents,extent_management,segment_space_managementfromdba_tablespaces;TABLESPACEBLOCK_SIZESTATUSCONTENTSEXTENT_MANSEGMENT_SPAC--------------------------------------------------------------SYSTEM8192ONLINEPERMANENTLOCALMANUALUNDOTBS18192ONLINEUNDOLOCALMANUALTEMP8192ONLINETEMPORARYLOCALMANUALINDX8192ONLINEPERMANENTLOCALAUTOTOOLS8192ONLINEPERMANENTLOCALAUTOUSERS8192ONLINEPERMANENTLOCALAUTO#Tools表空间是LTM/ASSM的#Step2.1initiateademotabtable2.1.1createtabledemotabinTOOLStablespaceDroptabledemotab;createtabledemotab(idvarchar2(20),namevarchar2(20))tablespacetools;2.1.2querydemotabsegmentselectowner,segment_name,tablespace_name,header_file,header_blockfromdba_segmentswhereowner='SYS'andsegment_name='DEMOTAB';OWNERSEGMENT_NAMETABLESPACEHEADER_FILEHEADER_BLOCK----------------------------------------------------------------------------SYSDEMOTABTOOLS4112.1.3querydba_extenttoshowdemotabsegment'scharacterselectowner,segment_name,tablespace_name,extent_id,file_id,block_idfromdba_extentswhereowner='SYS'andsegment_name='DEMOTAB';OWNERSEGMENT_NATABLESPACEEXTENT_IDFILE_IDBLOCK_ID----------------------------------------------------------------------SYSDEMOTABTOOLS049#DuetoTOOLStablespaceisanASSMtablespace,thefollowingiscorrect:##Block9isFIRSTLEVELBITMAPBLOCK.##Block10isSECONDLEVELBITMAPBLOCK.##Block11isPAGETABLESEGMENTHEADER,anditsHWMwillpointtothenextblock:block12.##之所以查以上两个dictionaryview,是因原以为数据必定写入block13,但实际却不是,最终根据ROWID发现数据是写到block15中去的.#2.1.4appendthreerecordsintodemotabtableinsertintodemotab(id,name)values('hijklmn',’11’);insertintodemotab(id,name)values('123',’22’);insertintodemotab(id,name)values('test',’33’);commit;selectrowid,id,namefromdemotab;ROWIDIDNAME---------------------------------------------------------------AAABfTAAEAAAAAPAAAhijklmn11AAABfTAAEAAAAAPAAB12322AAABfTAAEAAAAAPAACtest33#先查出ROWID,再调用sys.dbms_rowid.rowid_info获得fileid,blockid等信息#execshow_rowid('AAABfTAAEAAAAAPAAA');rowid_type..............................1object_number...........................6099relative_fno............................4block_number............................15row_number..............................0#Nowweknowthoseinsertedrowsarestoringinfile4andblock15.#2.1.5dumporiginaldatablock—beforebeingupdatedaltersystemdumpdatafile4block15;Startdumpdatablockstsn:4file#:4minblk15maxblk15buffertsn:4rdba:0x0100000f(4/15)scn:0x0000.0001f2a1seq:0x01flg:0x00tail:0xf2a10601frmt:0x02chkval:0x0000type:0x06=transdataBlockheaderdump:0x0100000fObjectidonBlock?seg/obj:0x17d3csc:0x00.1cad2itc:2flg:Etyp:1-DATAbrn:0bdba:0x1000009ver:0x01inc:0exflg:0#ITLentries#ItlXidUbaFlagLckScn/Fsc0x010x0009.020.000000360x00800092.0017.40--U-3fsc0x0000.0001c9dd#Xid0,Lck=3,Flag--U-(transactioncommitted(maybelongago);SCNisanupperbound).#0x020x0000.000.000000000x00000000.0000.00----0fsc0x0000.00000000#Xid=0,Lck=0,Flag----(notusedslot)#data_block_dump,dataheaderat0x5833064===============tsiz:0x1f98hsiz:0x18pbl:0x05833064bdba:0x0100000f76543210flag=--------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x1f75avsp=0x1f5dtosp=0x1f5d0xe:pti[0]nrow=3offs=00x12:pri[0]offs=0x1f8a0x14:pri[1]offs=0x1f800x16:pri[2]offs=0x1f75block_row_dump:tab0,row0,@0x1f8atl:14fb:--H-FL--lb:0x1cc:2col0:[7]68696a6b6c6d6ecol1:[2]3131tab0,row1,@0x1f80tl:10fb:--H-FL--lb:0x1cc:2col0:[3]313233#Thesecondrowwhichwejustinserted.Id=’123’name=’22’#col1:[2]3232tab0,row2,@0x1f75tl:11fb:--H-FL--lb:0x1cc:2col0:[4]74657374col1:[2]3333end_of_block_dumpStep2.2updatedemotabtable2.2.1updatethetableupdatedemotabsetid='555'whereid='123';#Attention:IshouldletthissessionidleuntilStep2.4.#2.2.2dumpdatablock–afterupdating(beforerollback)altersystemdumpdatafile4block15;Startdumpdatablockstsn:4file#:4minblk15maxblk15buffertsn:4rdba:0x0100000f(4/15)scn:0x0000.0001cad2seq:0x01flg:0x00tail:0xcad20601frmt:0x02chkval:0x0000type:0x06=transdataBlockheaderdump:0x0100000fObjecti