oracle ORA-12519, TNS: 找不到合适的服务处理程序

ORA-12519, TNS:no appropriate service handler found

提问by dolly

I am closing the hibernate connection properly but i am getting this error after some clicks or after some page refreshes. whats the problem i cant understand. I have also checked the number of sessions its also increasing by the following command and program code is also given:

我正在正确关闭休眠连接，但在单击或刷新某些页面后出现此错误。我无法理解的问题是什么。我还通过以下命令检查了会话数也在增加，并且还给出了程序代码：

Managedbean code:

托管bean代码：

public List<TrxFile> getFileDetails() {
        //SessionFactory sf = HibernateUtill.HibernateUtil.getSessionFactory();
        Session sess = HibernateUtill.HibernateUtil.getSessionFactory().openSession();
        java.util.List l = null;
        try {
            Query q = sess.createQuery("from Files");
            l = q.list();
            Iterator itr = l.iterator();

            return l;
        } catch (HibernateException hbx) {
            hbx.printStackTrace();
        } finally {
            sess.flush();
            sess.clear();
            sess.close();
            //sf.close();
            HibernateUtill.HibernateUtil.getSessionFactory().close();
        }
        return l;
    }

error:

错误：

ERROR:   Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:XE

More detailed Error:

更详细的错误：

WARN:   HHH000342: Could not obtain connection to query metadata : Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:XE

Info:   HHH000400: Using dialect: org.hibernate.dialect.OracleDialect
WARN:   HHH000063: The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead
WARN:   HHH000064: The OracleDialect dialect has been deprecated; use Oracle8iDialect instead
Info:   HHH000422: Disabling contextual LOB creation as connection was null
Info:   HHH000399: Using default transaction strategy (direct JDBC transactions)
Info:   HHH000397: Using ASTQueryTranslatorFactory
WARN:   SQL Error: 0, SQLState: null
ERROR:   Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:XE

Info:   HHH000043: Configuring from resource: /hibernate.cfg.xml
Info:   HHH000040: Configuration resource: /hibernate.cfg.xml
WARN:   HHH000223: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/. Use namespace http://www.hibernate.org/dtd/ instead. Refer to Hibernate 3.6 Migration Guide!
Info:   HHH000221: Reading mappings from resource: Pojos/TrxTender.hbm.xml
Info:   HHH000221: Reading mappings from resource: Pojos/CfgPaths.hbm.xml
Severe:   org.hibernate.exception.GenericJDBCException: Could not open connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:56)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.doPrepare(StatementPreparerImpl.java:161)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1859)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    at org.hibernate.loader.Loader.doQuery(Loader.java:900)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    at org.hibernate.loader.Loader.doList(Loader.java:2526)
    at org.hibernate.loader.Loader.doList(Loader.java:2512)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    at org.hibernate.loader.Loader.list(Loader.java:2337)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1269)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at ManagedBean.FileUpload.getFileDetails(FileUpload.java:560)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at javax.el.BeanELResolver.getValue(BeanELResolver.java:363)
    at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
    at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
    at com.sun.el.parser.AstValue.getValue(AstValue.java:140)
    at com.sun.el.parser.AstValue.getValue(AstValue.java:204)
    at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:226)
    at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109)
    at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
    at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
    at javax.faces.component.UIData.getValue(UIData.java:732)
    at org.primefaces.component.api.UIData.getDataModel(UIData.java:629)
    at javax.faces.component.UIData.getRowCount(UIData.java:356)
    at org.primefaces.component.api.UIData.calculateFirst(UIData.java:175)
    at org.primefaces.component.datatable.DataTableRenderer.preRender(DataTableRenderer.java:115)
    at org.primefaces.component.datatable.DataTableRenderer.encodeEnd(DataTableRenderer.java:83)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:924)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1863)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:176)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:894)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1856)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:443)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access0(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:XE
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:380)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:441)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java
Severe:   :839)
    at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:204)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
    ... 84 more

command to find the current sessions:

查找当前会话的命令：

SELECT COUNT(*)
  FROM v$session;

despite of closing sessions properly its increasing can anybody help plzz

尽管正确地结束了会议，但它的增加有人可以帮忙吗？

回答by Bjarte Brandt

Make sure your oracle listener offers the service 'XE'

确保您的 oracle 侦听器提供服务“XE”

  [bbrandt@limbo] ~/ $ lsnrctl status | grep XE
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
    Default Service           XE
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
    Service "XE" has 1 instance(s).
      Instance "XE", status READY, has 1 handler(s) for this service...
    Service "XEXDB" has 1 instance(s).
      Instance "XE", status READY, has 1 handler(s) for this service...
    [bbrandt@limbo] ~/ $

Make sure you are able to connect to the servicethrough sqlpluslike this before you try any other client applications:

在尝试任何其他客户端应用程序之前，请确保您能够像这样通过sqlplus连接到服务：

sqlplus username/password@localhost:1521/XE

Note! always connect to an oracle database using the SERVICE_NAME (hostname:1521/SERVICE_NAME). Connecting to an oracle database using the SID (hostname:1521:SID) .... is something oracle discouraged only 15 years ago!

笔记！始终使用 SERVICE_NAME (hostname:1521/SERVICE_NAME) 连接到 oracle 数据库。使用 SID (hostname:1521:SID) .... 连接到 oracle 数据库是 oracle 在 15 年前不鼓励的事情！

回答by Ishan Parikh

Try this solution at your end. It worked for me. Close the connection in try/catch block and just after closing the connection, write Thread.sleep(1000);

最后试试这个解决方案。它对我有用。在 try/catch 块中关闭连接，并在关闭连接后，写入Thread.sleep(1000);

You can write it as follows:

你可以这样写：

finally {
    try {
        if (conn != null && !conn.isClosed())
            {
            conn.close();
            Thread.sleep(1000);
             }
        }
    catch (SQLException e) {
        e.printStackTrace();
    }
}

回答by U_R_Naveen UR_Naveen

i also faced the same problem. the reasons for this error are:

我也面临同样的问题。此错误的原因是：

1. connections are not closed or idle for long time. due to this, the processes in oracle will exceed the limit.
2. Non-optimistic pool configuration in context.xml (apache tomcat).

1. 连接长时间未关闭或空闲。因此，oracle 中的进程将超过限制。
2. context.xml (apache tomcat) 中的非乐观池配置。

the steps i have followed to fix this issue are:

我为解决此问题而遵循的步骤是：

1. check current processes count by using select * from v$resource_limit where resource_name = 'processes';
2. Click PerformGC button in jconsole.exe which is available in bin folder of jdk installed folder.By doing so, idle connections which are not closed will be garbage collected.

1. 使用 select * from v$resource_limit where resource_name = 'processes' 来检查当前进程数；
2. 在 jdk 安装文件夹的 bin 文件夹中的 jconsole.exe 中单击 PerformGC 按钮。这样做，未关闭的空闲连接将被垃圾收集。

If above steps are not solved your problem, you can try by reducing maxIdle (The maximum number of connections that should be kept in the pool at all times.) in context.xml(apache tomcat)

如果以上步骤没有解决您的问题，您可以尝试在 context.xml(apache tomcat) 中减少 maxIdle（应始终保留在池中的最大连接数。）

Note : if heap size more , there is a possibility of more consumption of the db processes from the huge tenured Objects.

注意：如果堆大小更大，则可能会从巨大的终身对象中消耗更多的数据库进程。