This discussion is archived
4 Replies Latest reply: Mar 21, 2013 7:07 AM by oraman RSS

V$RESOURCE_LIMIT and TNS-12602 contradiction

oraman Newbie
Currently Being Moderated
hi experts,

I get regularely this error:

oracle TNS-12602: TNS: Connection Pooling limit reached

however according to this view there is no limit reached:

select * from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME          CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION               LIMIT_VALUE

processes                         94     138     600                         600
sessions                    107     220     1008                         1008


my environment:
oracle 11gr2
redhat

is my perception somehow wrong?


thank you
  • 1. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
    sb92075 Guru
    Currently Being Moderated
    oraman wrote:
    hi experts,

    I get regularely this error:

    oracle TNS-12602: TNS: Connection Pooling limit reached

    however according to this view there is no limit reached:

    select * from v$resource_limit where resource_name in ('processes','sessions');

    RESOURCE_NAME          CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION               LIMIT_VALUE

    processes                         94     138     600                         600
    sessions                    107     220     1008                         1008


    my environment:
    oracle 11gr2
    redhat

    is my perception somehow wrong?


    thank you
    does application utilize Connection Pooling?
    12602, 00000, "TNS: Connection Pooling limit reached"
    // *Cause:  The operation failed because maximum active current connections has
    //          been reached. It may not be a real error when the Connection
    //          Pooling feature is enabled. It is possible that the application
    //          later reissues the operation and successfully grabs the connection
    //          pool slot and proceeds.
    // *Action: This is an internal error. Enable tracing and attempt to repeat the
    //          error. If it occurs again, contact Oracle Customer
    //          Support.
  • 2. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
    TSharma-Oracle Guru
    Currently Being Moderated
    Do you have dedicated server environment OR MTS? If yes, can you post your mts dispatcher setting?
  • 3. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
    oraman Newbie
    Currently Being Moderated
    hello again,

    no I dont use mts:

    $ cat initmydb.ora | grep dispatcher
    *.dispatchers='(PROTOCOL=TCP)(DISPATCHERS=1)(SESSIONS=900)'
    $ cat initmydb.ora | grep mts
    no output here

    and no it seems like there is no Connection Pooling:
    client tnsnames.ora:

    conn1 =
    (description =
    (address = (host = server1)(protocol = tcp)(port = 1521))
    (address = (host = server2)(protocol = tcp)(port = 1521))
    (address = (host = server3)(protocol = tcp)(port = 1521))
    (failover = yes)
    (connect_data =
    (service_name = conn1.webadress.com)
    (failover_mode =
    (type = select)
    (method = basic)
    )
    )
    )

    I plan to change it to shared on all clients and hope that it will solve the issue!? :

    conn1 =
    (description =
    (address = (host = server1)(protocol = tcp)(port = 1521))
    (address = (host = server2)(protocol = tcp)(port = 1521))
    (address = (host = server3)(protocol = tcp)(port = 1521))
    (failover = yes)
    (connect_data =
    (service_name = conn1.webadress.com)
    (failover_mode =
    (type = select)
    (method = basic)
    )
    (SERVER=shared)
    )
    )

    or is something else the core of the issue?
  • 4. Re: V$RESOURCE_LIMIT and TNS-12602 contradiction
    oraman Newbie
    Currently Being Moderated
    any acknowledgment or criticism?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points