Forum Stats

  • 3,758,442 Users
  • 2,251,389 Discussions
  • 7,870,190 Comments

Discussions

Connection using SERVICE_NAME drops after 7 seconds

Prze-moC
Prze-moC Member Posts: 43 Red Ribbon
edited Dec 2, 2019 5:16AM in SQL Developer

Hi,

When I create connection in SQL Developer using Connection Type=Basic and SID (not Service Name) then everything is fine.https://i.stack.imgur.com/cvJz6.jpg

If only is the connection changed to use Basic+Service_Name or TNSNAMES then "funny" things start to happen.

"Funny" means that the connection works perfectly fine when running short statements (running less than 7 seconds). However, when the statement is longer then I get:

Error report - IO Error: An existing connection was forcibly closed by the remote hostError report - IO Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

For example I have a large package to compile (ca. 330KB). Package definition compiles fine, but package body compiles only if not exceeding certain size (ca. 50KB of code and 7 seconds compilation time).

I'm using latest SQL Developer (19.2.1) and latest Java (11.0.5) connecting to 12.2 RDBMS with encryption and checksums in SQLNET.ORA on the server:

SQLNET.ENCRYPTION_SERVER = required SQLNET.ENCRYPTION_TYPES_SERVER = (AES256) SQLNET.CRYPTO_CHECKSUM_SERVER = required SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA1) SQLNET.ENCRYPTION_CLIENT = required SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256) SQLNET.CRYPTO_CHECKSUM_CLIENT = required SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA1)

Does anyone have any clue what I'm experiencing here?

SQ

LNET.ORA on the server:

SQLNET.ENCRYPTION_SERVER = required SQLNET.ENCRYPTION_TYPES_SERVER = (AES256) SQLNET.CRYPTO_CHECKSUM_SERVER = required SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256, SHA1) SQLNET.ENCRYPTION_CLIENT = required SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256) SQLNET.CRYPTO_CHECKSUM_CLIENT = required SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256, SHA1)
Tagged:

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,980 Employee
    edited Nov 28, 2019 7:29PM

    Check your server logs, alert and listener...the server is killing your session.

  • Prze-moC
    Prze-moC Member Posts: 43 Red Ribbon
    edited Nov 29, 2019 7:13AM

    Alert.log is clean. Listener is also fine. I'm afraid it is SQL Developer that gives me the hard time.

    It must be the size of the package...

    When I tested today again. it showed me that the package compiled but hung on compiling the body. Actually it was before that. Because SQL Developer run sth else in between.

    I checked the hung session and it was waiting on "SQL*Net more data from client":

       SID,SEQ,EVENT,WAIT_TIME,SECONDS_IN_WAIT,STATE,P1,P1TEXT,P2,P2TEXT,P3,P3TEXT,Description

       689,264,SQL*Net more data from client,0,320,WAITING,1413697536,driver id,330314,#bytes,0,,Waiting for SQL*Net more data from client - waited 320 seconds, so far

    No locks.

    The SQL statement that was waiting was:

       SELECT LINE, POSITION, REPLACE (TEXT, CHR (10), ' '), attribute

       FROM SYS.ALL_ERRORS A

       WHERE A.NAME = :NAME AND A.TYPE = :TYPE AND A.OWNER = :OWNER

       ORDER BY ATTRIBUTE, LINE, POSITION          

    This is not part of my code, it is SQL Developer trying to show me that all went fine after compiling the package declaration. Or it actually showed me that already and just hung on reading the package body text...

    Tested again with tracing the sessions. Same results (I cannot make any sense of the traces anyway), with SID all OK, but with the SERVICE_NAME it hung (waits on "SQL*Net more data from client") on the above SQL.

    Again, when the package body size is below 40KB, all is OK even when on SERVICE_NAME. It is only when I exceed 40KB there. 

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,980 Employee
    edited Nov 29, 2019 1:41PM

    Version of Oracle Database?

    Error report - IO Error: An existing connection was forcibly closed by the remote host

    This is why I'm saying the database is ending the connection.

  • dvohra21
    dvohra21 Member Posts: 14,227 Gold Crown
    edited Dec 1, 2019 12:15PM

    It is only when I exceed 40KB there.

    Clearly, the issue is not due to SERVICE_NAME as the subject suggests but the PL/SQL object size that the network is not able to transmit. Please verify that none of the PL/SQL Program Limits are being exceeded.

    https://docs.oracle.com/database/121/LNPLS/limits.htm#LNPLS018

  • Prze-moC
    Prze-moC Member Posts: 43 Red Ribbon
    edited Dec 2, 2019 5:16AM

    SQL Developer 19.2.1, Java 11.0.5, RDBMS 12.2 Enterprise.

    No PL/SQL limits are exceeded as all compiles OK when connecting via SID.