MYSQL教程之ORA-01591妨碍处置
表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。凌晨到办公室听同事说表被锁了，一试，发明表中某字段为1111111的行都被锁了，SELECT都不可。报毛病ORA-01591，翻开TOAD的KnowledgeeXpert，形貌很少，只是说因为散布式事件毛病而形成锁定。扣问同事，今天经由过程一个存储历程挪用另外一个存储历程出了毛病，尔后者经由过程通明网关insert一些数据到SQlServer数据库。
当即想到翻开OEM，谁晓得事与愿违，进进锁，基本没发明相干的工具被锁定，入手下手有点忧郁。转而反省会话，该用户有5个会话，都是INACTIVE，不论三七二十一，全体杀失落。了局仍旧，而且锁也没有呈现。远程上岸上主机，发明CPU和历程都一般，也没有发明通明网关历程挂逝世（之前曾发明TG4SQL在无营业量时也会呈现25%摆布的CPU，挂逝世）。
俄然想到看看alert.log，经由细心搜刮，终究发明：
WedNov1700:00:042004
Errorsinfiled:oracleadminxdcjudumpxdcj_j006_3020.trc:
ORA-12012:主动实行功课82堕落
ORA-01591:锁定已被有成绩的分派事件处置6.5.887985挂起
ORA-06512:在line6
这恰是堕落的中央，往前追溯：
TueNov1617:35:042004
Error28500trappedin2PContransaction6.5.887985.Cleaningup.
Errorstackreturnedtouser:
ORA-02054:事件处置6.5.887985有成绩
ORA-28500:毗连ORACLE到非Oracle体系时前往此信息:
ORA-02063:紧接着2lines（源于ZSMOS_CRM）
TueNov1617:35:042004
DISTRIBTRANQDCJ.US.ORACLE.COM.5ae32328.6.5.887985
islocaltran6.5.887985(hex=06.05.d8cb1)
insertpendingpreparedtran,scn=6606197672830(hex=602.2010cb7e)
TueNov1617:35:072004
Errorsinfiled:oracleadminxdcjdumpxdcj_reco_3024.trc:
ORA-28500:connectionfromORACLEtoanon-Oraclesystemreturnedthismessage:
用户RECOVER登录失利。(SQLState:28000;SQLCode:18456)
ORA-02063:preceding2linesfromZSMOS_CRM
TueNov1617:35:122004
Errorsinfiled:oracleadminxdcjdumpxdcj_reco_3024.trc:
ORA-28500:connectionfromORACLEtoanon-Oraclesystemreturnedthismessage:
用户RECOVER登录失利。(SQLState:28000;SQLCode:18456)
ORA-02063:preceding2linesfromZSMOS_CRM
这就是事发地址了。看来是今天下战书远程事件失利，可是又没有前往形成散布式事件挂逝世，从而锁定了行。终究找到了具体的毛病ORA-02054，进进TOAD一查，说是要守候大概提交该事件，但是怎样操纵呢。仍是翻开官方文档搜刮相干内容，在AdminstratorGuide中发明以下内容：
DiscoveringProblemswithaTwo-PhaseCommit
Theuserapplicationthatcommitsadistributedtransactionisinformedofaproblembyoneofthefollowingerrormessages:
ORA-02050:transactionIDrolledback,
someremotedbsmaybein-doubt
ORA-02051:transactionIDcommitted,
someremotedbsmaybein-doubt
ORA-02054:transactionIDin-doubt
Arobustapplicationshouldsaveinformationaboutatransactionifitreceivesanyoftheaboveerrors.Thisinformationcanbeusedlaterifmanualdistributedtransactionrecoveryisdesired.
Noactionisrequiredbytheadministratorofanynodethathasoneormorein-doubtdistributedtransactionsduetoanetworkorsystemfailure.TheautomaticrecoveryfeaturesofOracletransparentlycompleteanyin-doubttransactionsothatthesameoutcomeoccursonallnodesofasessiontree(thatis,allcommitorallrollback)afterthenetworkorsystemfailureisresolved.
Inextendedoutages,however,youcanforcethecommitorrollbackofatransactiontoreleaseanylockeddata.Applicationsmustaccountforsuchpossibilities.
DeterminingWhethertoPerformaManualOverride
Overrideaspecificin-doubttransactionmanuallyonlywhenoneofthefollowingsituationsexists:
Thein-doubttransactionlocksdatathatisrequiredbyothertransactions.ThissituationoccurswhentheORA-01591errormessageinterfereswithusertransactions.
Anin-doubttransactionpreventstheextentsofarollbacksegmentfrombeingusedbyothertransactions.Thefirstportionofanin-doubtdistributedtransactionslocaltransactionIDcorrespondstotheIDoftherollbacksegment,aslistedbythedatadictionaryviewsDBA_2PC_PENDINGandDBA_ROLLBACK_SEGS.
Thefailurepreventingthetwo-phasecommitphasestocompletecannotbecorrectedinanacceptabletimeperiod.Examplesofsuchcasesincludeatelecommunicationnetworkthathasbeendamagedoradamageddatabasethatrequiresalongrecoverytime.
Normally,youshouldmakeadecisiontolocallyforceanin-doubtdistributedtransactioninconsultationwithadministratorsatotherlocations.Awrongdecisioncanleadtodatabaseinconsistenciesthatcanbedifficulttotraceandthatyoumustmanuallycorrect.
Iftheconditionsabovedonotapply,alwaysallowtheautomaticrecoveryfeaturesofOracletocompletethetransaction.Ifanyoftheabovecriteriaaremet,however,consideralocaloverrideofthein-doubttransaction.
看来是倡议差未几，前面Oracle老是试图登录SQlServer就是要主动恢复，但是总不乐成。观察视图DBA_2PC_PENDING的确发明了该事件的陈迹。要如何操纵呢？
ManuallyCommittinganIn-DoubtTransaction
Beforeattemptingtocommitthetransaction,ensurethatyouhavetheproperprivileges.Notethefollowingrequirements:
Ifthetransactionwascommittedby...Thenyoumusthavethisprivilege...
You
FORCETRANSACTION
Anotheruser
FORCEANYTRANSACTION
CommittingUsingOnlytheTransactionID
ThefollowingSQLstatementcommitsanin-doubttransaction:
COMMITFORCEtransaction_id;
Thevariabletransaction_idistheidentifierofthetransactionasspecifiedineithertheLOCAL_TRAN_IDorGLOBAL_TRAN_IDcolumnsoftheDBA_2PC_PENDINGdatadictionaryview.
Forexample,assumethatyouqueryDBA_2PC_PENDINGanddeterminethatLOCAL_TRAN_IDforadistributedtransactionis1:45.13.
YouthenissuethefollowingSQLstatementtoforcethecommitofthisin-doubttransaction:
COMMITFORCE1.45.13;
CommittingUsinganSCN
Optionally,youcanspecifytheSCNforthetransactionwhenforcingatransactiontocommit.Thisfeatureallowsyoutocommitanin-doubttransactionwiththeSCNassignedwhenitwascommittedatothernodes.
Consequently,youmaintainthesynchronizedcommittimeofthedistributedtransactionevenifthereisafailure.SpecifyanSCNonlywhenyoucandeterminetheSCNofthesametransactionalreadycommittedatanothernode.
Forexample,assumeyouwanttomanuallycommitatransactionwiththefollowingglobaltransactionID:
SALES.ACME.COM.55d1c563.1.93.29
First,querytheDBA_2PC_PENDINGviewofaremotedatabasealsoinvolvedwiththetransactioninquestion.NotetheSCNusedforthecommitofthetransactionatthatnode.SpecifytheSCNwhencommittingthetransactionatthelocalnode.Forexample,iftheSCNis829381993,issue:
COMMITFORCESALES.ACME.COM.55d1c563.1.93.29,829381993;
SeeAlso:
Oracle9iSQLReferenceformoreinformationaboutusingtheCOMMITstatement
ManuallyRollingBackanIn-DoubtTransaction
Beforeattemptingtorollbackthein-doubtdistributedtransaction,ensurethatyouhavetheproperprivileges.Notethefollowingrequirements:
Ifthetransactionwascommittedby...Thenyoumusthavethisprivilege...
You
FORCETRANSACTION
Anotheruser
FORCEANYTRANSACTION
ThefollowingSQLstatementrollsbackanin-doubttransaction:
ROLLBACKFORCEtransaction_id;
Thevariabletransaction_idistheidentifierofthetransactionasspecifiedineithertheLOCAL_TRAN_IDorGLOBAL_TRAN_IDcolumnsoftheDBA_2PC_PENDINGdatadictionaryview.
Forexample,torollbackthein-doubttransactionwiththelocaltransactionIDof2.9.4,usethefollowingstatement:
ROLLBACKFORCE2.9.4;
因而上岸数据库
COMMITFORCE6.5.887985;
然后检察DBA_2PC_PENDING发明形态已改成COMMITFORCE，SELECT该表相干行，统统一般。至此，妨碍办理。
整体来看，间接INSERT...TABLENAME@SQLDBLK仍是很伤害的，赶上不克不及一般前往就出成绩了。Oracle的文档是保举利用包大概存储历程来办理，今后倡议同事改用此办法，今朝已测试经由过程。
与数据库相关的流程的逐渐标准化，使得解决方案提供商能以更便捷的方式提供服务、部署应用程序、规划容量和管理资源。DBaaS模式还有助于减少数据和数据库的冗余度并提升整体服务质量。 我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力 对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。 对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。 而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。 不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理? 对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。
页:
[1]