JDBC连接Oracle数据库常见问题及解决方法

注:本文是斑竹从JDBC&Transaction版摘录广大站友的提问以及各种解答整理而来，如果您认为本文的内容已触犯了您的权益，请联系管理员进行修改。

1. Jbuilder正确连接oracle9i需要注意的几个问题
   • oracle8以上的应该都使用classes12.jar文件作为jdbc驱动；
   • 正确设置windows的classpath和jbuilder中的enterprisesetup、configurelibraries，将casses12.jar路径正确添加到上述需要设置的地方；
   • 进入databasepilot，在databasepilot中，file---new在driver列表中如果oracle的驱动是红色的，就说明你的oralce在jb中加载失败；
   • 若③没有错，则新建一个url：jdbc:oracle:thin:@（yourhostname）:1521:（yoursid）
   • 连接数据库的Username/password

   注:大部分站友的jbuilder连接oracle的问题都是由于没有正确配置classpath等引起的。

2. 使用非XA方式连接ORACLE数据库

   在windows下配置Oracle_XA时要注意两点：

   #ORACLE10
   Oracle_XA;xaosw;%ORACLE_HOME%dbmsoraxa10.lib%ORACLE_HOME%precompibmsvcorasql10.lib

   #oracle9
   Oracle_XA;xaosw;%ORACLE_HOME%dbmsoraxa9.lib%ORACLE_HOME%precompibmsvcorasql9.lib

   Unix下
   Oracle_XA:xaosw:....

   第一：在windows下ORACLE_XA和xaosw后面的是分号";"，不是冒号":"
   第二：上面的这些LIB写在一行上，中间用空格分开，如果没有在系统的环境变量中设置ORACLE_HOME，就写绝对路径。

3. JDBC操作ORACLE数据库时出现‘java.sql.SQLException:IO异常，不在流模式下"
   1. 用OracleStatment，，不要用java.sql.Statment
   2. 如果对已有连接进行setAutoCommit失败，则关闭该连接并重新建立一个连接
   3. 到ORACLE站点下载一个最新的JDBCDriver，如果操作LOB类型，用ORACLE自带的接口和类
4. weblogic连接oracle问题：TheNetworkAdaptercouldnotestablishtheconnection

   可能是服务器的监听停掉了，是数据库的问题，与应用无关；应该先检查一下oracle是否正常，用sql*plus连接一下数据库，看能否正常连接；

5. Weblogic中使用Oracle连接池及Oracle备份的注意事项

   使用HP-UNIX，Weblogic8.1,Oracle9.2.0.5
   配置了一个普通的连接池,驱动程序采用oracle的Oracle"sDriver(Thin)version9.0.1,9.2.0

   错误情况：
   结果使用数据库连接池时报错，说没有连接池资源了。实际上数据库的连接池完全空闲，并且测试也是对的，Oracle也是正常可以连接、使用的。

   问题根源：
   通过层层排错，发现原来后台在使用Oracle的exp备份一个只有同义词的用户，导致exp进程僵死。
   杀死exp、重启Oracle等无法解决问题，最终重启UNIX，禁止备份只有同义词的用户，问题解决。

   总结：
   应该是Oracle9的expBUG导致连接池问题，不要使用exp倒出同义词

6. 连接Oracle时抛出如下异常：java.sql.SQLException:Ioexception:TheNetworkAdaptercouldnotestablishconnection一种产生原因

   OracleDatabaseConnection(fromoracle.com)
   PROBLEM
   YouareattemptingtoconnecttoanOracleinstanceusingJDBCandyouarereceivingthefollowingerror. 
     java.sql.SQLException:Ioexception:
       TheNetworkAdaptercouldnotestablishconnection
     SQLException:SQLState(null)vendorcode(17002)
   Anyorallofthefollowingconditionsmayalsoapply:
   1)YouareabletoestablishaSQL*Plusconnectionfromthesame
     clienttothesameOracleinstance.
   2)YouareabletoestablishaJDBCOCIconnection,butnotaThin
     connectionfromthesameclienttothesameOracleinstance.
   3)ThesameJDBCapplicationisabletoconnectfromadifferent
     clienttothesameOracleinstance.
   4)ThesamebehaviorapplieswhethertheinitialJDBCconnection
     stringspecifiesahostnameoranIPaddress.
   REDISCOVERY 
   Toverifywhetheryouarehittingthisproblem,verifywhethertheOracleinstanceisconfiguredforMultithreadedServer(MTS).IftheOracleinstanceisnotconfiguredforMTS,youareprobablyencounteringadifferentproblem.Otherwise,continue.TryforcingtheJDBCconnectiontouseadedicatedserverinsteadofasharedserver.Thiscanbeaccomplishedinseveralways.ForJDBCOCIorThin,thiscanbedonebyreconfiguringtheserverfordedicatedconnectionsonly.Thisapproach,however,maynotbefeasibleinmanycases.Insuchcases,thefollowingoptionsapply:ForJDBCOCI:  
   1)Addthe(SERVER=DEDICATED)propertytotheTNSconnectstring
     storedinthetnsnames.orafileontheclient.
   2)Settheuser_dedicated_server=ONinsqlnet.oraontheclient.
   ForJDBCThin:
   Youmustspecifyafullname-valuepairconnectstring(thesameasitmightappearinthetnsnames.orafile)insteadoftheshortJDBCThinsyntax.Forexample,insteadof
   "jdbc:oracle:thin::port:sid"
   youwouldneedtouseastringoftheform
   "jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="             +
                             "(ADDRESS=(PROTOCOL=TCP)"+
                                      "(HOST=host)"   +                                     =
                                      "(PORT=port)"   +
                             ")"                      +
                         ")"                          +
                         "(CONNECT_DATA="             +
                             "(SERVICE_NAME=sid)"     +
                             "(SERVER=DEDICATED)"     +
                         ")"                          +
                       ")"
   Iftheconnectionworksfineafterhavingmadethesechanges,itisverylikelythatthisistheproblemyouareencountering.Inthiscase,onelasttestwillhelptoverifythisfact.
   LogintotheremotehostonwhichtheOracleinstanceisrunningandexecutetheappropriatecommandtodeterminewhattheserver"thinks"itshostnameis(i.e.thenamethatwasconfiguredwhentheserverwasinstalledandconfigured).Forexample,onaUnixhostthe"hostname"commandcanbeusedforthispurpose.
   Usingthenamedisplayed(e.g.bythehostnamecommand),exactlyasitappeared(i.e.iftheoutputfromthehostnamecommandhadthedomainnameincluded,thenincludeit),returntotheclientwhichwasunabletoconnectandtrypingingtheserver.
   NOTE:ItiscriticalthatyouattempttopingtheserverusingEXACTLYthesamehostnameyougotfromtheserver.
   Ifyouareunabletopingtheserverviathishostname,thenyoualmostcertainlyhittingthisproblem.Ifnot,thismaybeanewissue,butatleastyouwillhavefoundaworkaround(i.e.useadedicatedconnection).
   EXPLANATION 
   Tounderstandwhythisproblemoccurs,onemustfirstunderstandthedifferencesinhowthelistenerhandlesconnectionstosharedserversversusdedicatedservers.
   Whenconnectingtoadedicatedserver,theclientconnectstothelistener(viahostnameorIPaddress).Thelistenerthenspawnsadedicatedserverprocessandhandsoffthesocketusedtoaccepttheclientconnectiontothatserver.Theclientandserverthenstartcommunicatingviatheendpointsestablishedbytheinitialconnection.NOTE:Thereisonlyoneconnectioninthiscase.Whenconnectingtoasharedserver,theinitialclientconnectiontothelisteneristhesame.However,withMTS,thereisnoneedtospawnanewserverprocess;apoolofsharedprocessesalreadyexists.Also,clientsdonotcommunicatedirectlywiththeserverprocessesinMTS;rather,theycommunicatewithadispatcher.
   Forthisreason,whensettingupanMTSconnection,thelistenersendsaredirectmessagebacktotheclientaskingtheclienttoclosetheconnectiontothelistenerandconnecttoadispatcher.Theinformationinthismessageincludesthehostnameandaportnumberfortheappropriatedispatcher.TheredirectmessagewillALWAYSspecifyahostname,eveniftheclientinitiallyprovidedanIPaddress.
   If,foranyreason,thehostnameprovidedtothelistener(e.g.bythe"hostname"oranothercommand)doesn"tagreewiththehostnamebywhichtheserverisknownontheclient,theconnectionfails.
   Ontheotherhand,if"(SERVER=DEDICATED)"alreadyappearsintheTNSconnectstringintnsnames.oraorif"use_dedicated_server=ON"alreadyappearsinthesqlnet.orafile,youmayfindthatSQL*Plusand/orJDBCOCIworkfine,whileJDBCThinfails.
   SOLUTION 
   Obviously,onesolutionistousededicatedservers.However,thismaynotalwaysbefeasible.
   Thekeyistomakesurethehostnameonboththeclientandserveragree.Thiscanbeaccomplishedbyreconfiguringeithertheclientortheserver,buttherearethingstobeawareofinbothcases.
   Iftheserverisconfiguredtoreturnadifferenthostname,thenitispossiblethatotherclientswhichusedtoworkwillnowfail.
   Insomecases,itmaynotbefeasibletoreconfiguretheclient.Forexample,iftheserverversionofthehostnamedoesnotincludethedomain,youwouldneedtoremovethedomainportionofthehostnameontheclient;but,iftheclientneedstoconnecttomorethanoneserverwiththesamebasenameindifferentdomains,thismaynotbepossible,asthehostnamemaybeambiguous.
   REFERENCES 
   bug:1269734java.sql.SQLException:Ioexception:TheNetworkAdaptercouldnotbefound.

7. 连接ORACLE数据库报错：javax.naming.NameNotFoundException:UnabletoresolveoracThin.Resolved:""Unresolved:"oracThin";remainingname"" 

   问题描述：配置完JDBC后，打开页面的时候，报出如下错误信息：
   javax.naming.NameNotFoundException:UnabletoresolveoracThin.Resolved:""Unresolved:"oracThin";remainingname""
   JDBC配置如下：
   ConnectionPools(连接池)
   Name:OracThin
   URL:jdbc:oracle:thin.0.0.1:LYSIMIS
   DriverClassname:oracle.jdbc.driver.OracleDriver
   Properties:
   user=system
   password=manager
   dll=ocijdbc8
   protocol=thin
   数据源配置如下：
   Name:OracThin
   JNDIName:OracThin
   PoolName:OracThin
   当程序执行到这一步时出错。
   ctx=newInitialContext();
   ds=(javax.sql.DataSource)ctx.lookup("OracThin"); 

   问题解决后汉字是乱码 

   错误产生原因及解决办法：

   • URL:jdbc:oracle:thin:.0.0.1:1521:LYSIMI，thin后面加:,127.0.0.1后面加端口号
   • 注意名字大小写.
   • target到server上
   • 再看看pool是否起来了，没起来的话,重起weblogic
   • 乱码问题（Java是基于Unicode）：
     • 在JSP文件中加入<%@pagecontentType="text/html;charset=GBK"%>
     • 在weblogic.xml文件的<jsp-descriptor>部分加入
       <jsp-descriptor>
       <jsp-param>
       <param-name>compilerSupportsEncoding</param-name>
       <param-value>true</param-value>
       </jsp-param>
       <jsp-param>
       <param-name>encoding</param-name>
       <param-value>GBK</param-value>
       </jsp-param>
       </jsp-descriptor>
8. oracleXA的疑惑

   问题描述：Oracle_XA;xaosw;D:oracleora92dbmsXAORAXA9.libC:msvcoraSQL9.lib中
   xaosw是什么意思

   解答：可以参考ORACLE的XA部分的文档。

   http://www-rohan.sdsu.edu/doc/oracle/server803/A54642_01/ch_xa.htm

9. oracle与weblogic自动启动与停止

   问题描述：每次重新启动服务器时oracle数据库若没有关闭，则必须先关闭后在重新启动redhatadvanceserver，oracle才能够正常运行

   原因及解决办法参见：

   http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=81&threadID=8839&messageID=43184#43184

本文整理的只是本版文章的一小部分，如果这里没有解答您的问题，请查阅本版的精华区，也可以使用论坛的搜索功能。

整理日期2005.9.26