Home » RDBMS Server » Performance Tuning » No more data to read from socket (No more data to read from socket (Oracle error 17410 ))
No more data to read from socket [message #671900] Sat, 22 September 2018 13:20 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Some times I am getting the No more data to read from socket error,
when we are doing the index enabling or disabling on LIST partitioned table.
To enable or disable the index we have the PROC_INDEX_ENABLE_DISABLE procedure.

Following are the situation we need to handle .

1: All the table are LIST Partitioned on the BRANCH columns like ABC,XYZ,1BB,
(Branch value may start with Number also).
Ideally we will use same name for Partition name also.
We Oracle doesn't allow us to create the Objects start with number,
we had created different partition name when it is holding the value start with Number.

2: Multiple branches may belongs to one group id. If we pass the Group id all the branches under the
same group id corresponding branch related partitions have to be enabled/disabled

If i am running individually it is working fine.But this enabling /enabling is called in multiples procedure
with different tables,different group ids.

some times I am getting the Oracle Internal error

CallableStatementCallback; uncategorized SQLException for SQL [{call PROC_INDEX_ENABLE_DISABLE(?, ?, ?, ?)}]; 
SQL state [null]; error code [17410]; 
No more data to read from socket; nested exception is java.sql.SQLException: No more data to read from socket


CREATE TABLE  GROUP_BRANCHES(BRN_GROUP_ID  VARCHAR2(20), BRANCH_NAME VARCHAR2(20));
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('B1', 'XYZ');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('ABC', 'ABC');
INSERT INTO "GROUP_BRANCHES" (BRN_GROUP_ID, BRANCH_NAME) VALUES ('XYZ', 'XYZ');
DROP TABLE  LIST_PARTITIONED_TABLE; 
CREATE TABLE LIST_PARTITIONED_TABLE(BRANCH VARCHAR2(4 CHAR) NOT NULL ENABLE,REFERENCE_NUMBER VARCHAR2(20 CHAR) NOT NULL ENABLE )
PARTITION BY LIST( BRANCH)( PARTITION ABC VALUES('0ABC'));
create or replace TYPE SCHARTABLE  IS TABLE OF VARCHAR2(20);


create or replace PROCEDURE PROC_INDEX_ENABLE_DISABLE(
    v_table_name IN VARCHAR2,
    vMode         IN VARCHAR2,
    vStatus       IN OUT VARCHAR2,
    v_brn_group_id       IN VARCHAR2) 
IS
  v_Index_Name      VARCHAR2(30);
  v_Constraint_Name VARCHAR2(30);
  v_Process_Time    NUMBER;
  v_Owner_Name      VARCHAR2(10) := 'schemaname';
  vErrStr           VARCHAR2(4000);
  v_MV_Wait_Time    NUMBER;
  v_isJobNameExists USER_JOBS.JOB%TYPE;
  C_START_STATUS VARCHAR(2)                  := '-1';
  C_FINAL_STATUS VARCHAR(1)                  := '0';
  C_PRGNAME   VARCHAR(200):= 'PROC_INDEX_ENABLE_DISABLE';
  C_STARTED     VARCHAR2(7)                      := 'STARTED';
  C_COMPLETED   VARCHAR2(10)                     := 'COMPLETED';
  C_ERROR_STACK VARCHAR2(20)                     := 'Error_Stack...';
  C_FAILURE     VARCHAR2(10)                     := 'F';
  C_SUCCESS     VARCHAR2(10)                     := 'S';
  list_partition_name schartable                 := schartable();
TYPE tbPartitioned
IS
  TABLE OF USER_IND_PARTITIONS.PARTITION_NAME%TYPE;
  tPartitioned tbPartitioned;
TYPE tbIndex_Name
IS
  TABLE OF USER_INDEXES.INDEX_NAME%TYPE;
  tIndex_Name tbIndex_Name;
  PROCEDURE SP_LOAD_PARTITIONS
  IS
  BEGIN
    IF vMode IN ('D','E') THEN
      SELECT PARTITION_NAME bulk collect
      INTO list_partition_name
      FROM XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE ('SELECT TABLE_NAME, HIGH_VALUE ,PARTITION_NAME FROM USER_TAB_PARTITIONS U') COLUMNS TABLE_NAME VARCHAR2(100), HIGH_VALUE VARCHAR2(200), PARTITION_NAME VARCHAR2(200)) XT ,
        GROUP_BRANCHES MBI
      WHERE TRIM(MBI.BRANCH_NAME) = REPLACE( TRIM(XT.HIGH_VALUE),'''',NULL)
      AND trim(MBI.BRN_GROUP_ID)     = trim(v_brn_group_id)
      AND XT.TABLE_NAME             = v_table_name;
    END IF ;
  END SP_LOAD_PARTITIONS;
  PROCEDURE SP_DELETE_PARTITIONS
  IS
  BEGIN
    IF vMode IN ('D','E') THEN
      list_partition_name.delete;
    END IF ;
  END SP_DELETE_PARTITIONS;
  PROCEDURE SP_NON_PARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR echIndxNM IN
    (SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE STATUS    = 'UNUSABLE'
    AND TABLE_NAME  = v_table_name
    AND TABLE_OWNER = v_Owner_Name
    AND PARTITIONED = 'NO'
    )
    LOOP
      --Rebuilding the Indexes
      EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' REBUILD ONLINE PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||echIndxNM.INDEX_NAME ||' PARALLEL 1';
    END LOOP;
  END SP_NON_PARTITION_IDXS_ENABLE;
  PROCEDURE SP_PARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIP.PARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_IND_PARTITIONS UIP ,
      USER_INDEXES UI,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME = UIP.INDEX_NAME
    AND UI.INDEX_NAME   = UPI.INDEX_NAME
    AND UI.TABLE_NAME   = UPI.TABLE_NAME
    AND UI.TABLE_NAME   = v_table_name
    AND UI.TABLE_OWNER  = v_Owner_Name
    AND UIP.STATUS      = 'UNUSABLE'
    AND PARTITIONED     = 'YES'
    AND UIP.COMPOSITE   ='NO'
    AND 1               =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME||' REBUILD PARTITION '||rec_indx.PARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.INDEX_NAME ||' PARALLEL 1';
    END LOOP;
  END SP_PARTITION_IDXS_ENABLE;
  PROCEDURE SP_SUBPARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME BULK COLLECT
    INTO tPartitioned ,
      tIndex_Name
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS        IN ('UNUSABLE','N/A')
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name||' REBUILD SUBPARTITION '||rec_indx.SUBPARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name ||' PARALLEL 1';
    END LOOP;
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' ENABLE PRIMARY KEY';
    END IF;
  END SP_SUBPARTITION_IDXS_ENABLE;
  PROCEDURE SP_NON_PARTITION_IDXS_DISABLE
  IS
  BEGIN
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' DISABLE PRIMARY KEY';
    END IF;
    --1: All Non Partitioned index on Partitioned/Non Partitioned  table
    FOR n_Idx IN
    (SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE STATUS    = 'VALID'
    AND TABLE_NAME  = v_table_name
    AND TABLE_OWNER = v_Owner_Name
    AND PARTITIONED = 'NO'
    )
    LOOP
      EXECUTE IMMEDIATE 'ALTER INDEX '||n_Idx.INDEX_NAME||' UNUSABLE';
    END LOOP;
  END SP_NON_PARTITION_IDXS_DISABLE;
  PROCEDURE SP_PARTITION_IDXS_DISABLE
  IS
  BEGIN
    FOR rec_idx IN
    ( SELECT DISTINCT UIP.PARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_IND_PARTITIONS UIP ,
      USER_INDEXES UI,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME = UIP.INDEX_NAME
    AND UI.INDEX_NAME   = UPI.INDEX_NAME
    AND UI.TABLE_NAME   = UPI.TABLE_NAME
    AND UI.TABLE_NAME   = v_table_name
    AND UI.TABLE_OWNER  = v_Owner_Name
    AND UIP.STATUS      = 'USABLE'
    AND PARTITIONED     = 'YES'
    AND UIP.COMPOSITE   ='NO'
    AND 1               =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Disabling the Indexes at Partitions level.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_idx.INDEX_NAME||' MODIFY PARTITION '||rec_idx.PARTITION_NAME|| ' UNUSABLE ';
    END LOOP;
  END SP_PARTITION_IDXS_DISABLE;
  PROCEDURE SP_SUBPARTITION_IDXS_DISABLE
  IS
  BEGIN
    FOR REC_INDX IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS         ='USABLE'
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding theIndexes at SUBPartitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||REC_INDX.INDEX_NAME||' MODIFY SUBPARTITION '||REC_INDX.SUBPARTITION_NAME || ' UNUSABLE ';
    END LOOP;
  END SP_SUBPARTITION_IDXS_DISABLE;
BEGIN
  vStatus := C_START_STATUS;
  SP_LOAD_PARTITIONS;
  -- Gets the Constraint_Name for table passed as v_table_name.
  BEGIN
    SELECT CONSTRAINT_NAME
    INTO v_Constraint_Name
    FROM USER_CONSTRAINTS
    WHERE TABLE_NAME   =v_table_name
    AND CONSTRAINT_TYPE='P'
    AND OWNER          = v_Owner_Name;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_Constraint_Name := NULL;
  END;
  -----------------------------------------------------------------------------------------
  -----------------------------------------------------------------------------------------
  /* If vMode = 'D' i.e, DISABLE, then
  (1) Fetches the all Indexes of the Table and Disables the same
  (2) If the Table has Primary Key Constraint then Disables it.
  */
  IF vMode = 'D' THEN
    SP_NON_PARTITION_IDXS_DISABLE; --To Disable the non partitioned indexes for the given table
    SP_PARTITION_IDXS_DISABLE;     --To Disable the partitioned indexes for the given table
    SP_SUBPARTITION_IDXS_DISABLE;  --To Disable the sub partitioned indexes for the given table
  Elsif vMode = 'E' THEN
    SP_NON_PARTITION_IDXS_ENABLE;--To Enable the non partitioned indexes for the given table
    SP_PARTITION_IDXS_ENABLE;    --To Enable the  partitioned indexes for the given table
    SP_SUBPARTITION_IDXS_ENABLE; --To Enable the sub partitioned indexes for the given table

  END IF;

  SP_DELETE_PARTITIONS;
  
EXCEPTION
WHEN OTHERS THEN
  vErrStr:= SUBSTR('Error_Stack...' || DBMS_UTILITY.FORMAT_ERROR_STACK() || Chr(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),1,3500);
   vStatus := C_START_STATUS;
END PROC_INDEX_ENABLE_DISABLE;

Thanks
SaiPradyumn
Re: No more data to read from socket [message #671901 is a reply to message #671900] Sat, 22 September 2018 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
I doubt that you'll readily accept my comments, but with free advice sometimes you get what you paid for it.
Remove, delete & eliminate the WHEN OTHER exception handler since it does more harm than good.
http://www.orafaq.com/wiki/WHEN_OTHERS
When will vStatus return a value different from -1? What does vStatus=-1 mean?
>SQL state [null]; error code [17410];
Oracle does NOT have an error code=17410; AFAIK. From where does this value originate?

IMO, a bug exist with the Java driver code & NOT within PL/SQL procedure.
I believe that Oracle is the victim, not the culprit.

Your comments & answers are welcomed.
Re: No more data to read from socket [message #671905 is a reply to message #671901] Sat, 22 September 2018 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle has no more a "No more data to read from socket" error.

Re: No more data to read from socket [message #671912 is a reply to message #671905] Mon, 24 September 2018 01:45 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi BlackSwan,

Really this Forum is very helpful to learn so many concepts and to resolve my issues.
Sincerely I am following all the suggesting provided by the forum.I am very thankful to this forum always.

In WHEN OTHERS THEN block have one more procedure call which will inserts all error message into exception log table. When return value from the procedure is -1 we will treat this one as failure case and we will come to know about the exceptions in the log table.

We have the shell script which will invokes the JAVA application, From that it will call the ORACLE Procedure,
depending the on the value returned by procedure we will make the procedure execution as success or failure .
If it is failure we will update the corresponding error message also.We got this error message from this place.

Previously this procedure is working fine. At that time all tables are non-partitioned table.
Recently we had converted those table to LIST Partitioning in order to give the support for multiple branches.

Following is major change which we had implemented in this version:


SELECT PARTITION_NAME bulk collect
      INTO list_partition_name
      FROM XMLTABLE('/ROWSET/ROW' PASSING DBMS_XMLGEN.GETXMLTYPE ('SELECT TABLE_NAME, HIGH_VALUE ,PARTITION_NAME FROM USER_TAB_PARTITIONS U') COLUMNS TABLE_NAME VARCHAR2(100), HIGH_VALUE VARCHAR2(200), PARTITION_NAME VARCHAR2(200)) XT ,
        GROUP_BRANCHES MBI
      WHERE TRIM(MBI.BRANCH_NAME) = REPLACE( TRIM(XT.HIGH_VALUE),'''',NULL)
      AND trim(MBI.BRN_GROUP_ID)     = trim(v_brn_group_id)
      AND XT.TABLE_NAME             = v_table_name;
    END IF 

1:XMLTABLE
2:BULK COLLECT
3:COLLECTION (Single dimension TYPE Variable)

So every one is suspecting any one of above is causing the error

Thanks
SaiPradyumn


Re: No more data to read from socket [message #671921 is a reply to message #671912] Mon, 24 September 2018 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Sun, 23 September 2018 23:45
Hi BlackSwan,

Really this Forum is very helpful to learn so many concepts and to resolve my issues.
Sincerely I am following all the suggesting provided by the forum.I am very thankful to this forum always.

In WHEN OTHERS THEN block have one more procedure call which will inserts all error message into exception log table. When return value from the procedure is -1 we will treat this one as failure case and we will come to know about the exceptions in the log table.

You ignored my question about how does procedure ever return vStatus not equal -1 directly.
I imply from your response that the posted code is not the truth, the whole truth & nothing but the truth.
Posted code is a modified & obfuscated code version.
Am I correct?

Please post URL from Oracle documentation where error code = 17410.

>So every one is suspecting any one of above is causing the error
You are entitled to your opinion regardless the total lack of proof how above could be true.

Please confirm that the Java code was also changed to adjust to the change in underlying table structure.
Re: No more data to read from socket [message #671924 is a reply to message #671921] Mon, 24 September 2018 09:30 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi BlackSwan,

I had modified the all the object names and removed the dependent some other procedures also.
By mistake the following line is also removed at the end before the exception.

vStatus := C_FINAL_STATUS;

One more change with respect to JAVA Is version update from 1.6 to 1.8 .
this error we are getting when we are calling this procedure through the shell script & java Application.
If i am running through SQL Developer its working fine.

Thanks
SaiPradyumn
Re: No more data to read from socket [message #671925 is a reply to message #671924] Mon, 24 September 2018 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
I'll stop wasting my time trying to convince you the root cause resides within the Java code.
You are free to focus exclusively on the Oracle procedure as far as I am concerned.

http://lmgtfy.com/?q=oracle+error+code+17410

You're On Your Own (YOYO)!
Re: No more data to read from socket [message #671927 is a reply to message #671924] Mon, 24 September 2018 16:07 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Mon, 24 September 2018 07:30

this error we are getting when we are calling this procedure through the shell script & java Application.
If i am running through SQL Developer its working fine.

Thanks
SaiPradyumn

Please post a simple, reproducible test case which throws 17410 error; so we can see the same as your report.
Re: No more data to read from socket [message #671932 is a reply to message #671927] Tue, 25 September 2018 01:34 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan,

As you said I am unable to replicate the issue from SQL Developer .Even through the Linux also its not coming every time.Some times we are getting this problem.Don't have any clue when it is working fine and when it is giving the exception

As you suggested, asked the Java team to investigate from their side also.

Thank you very much for your interest to solve the issue.

Thanks
SaiPradyumn
Re: No more data to read from socket [message #671933 is a reply to message #671932] Tue, 25 September 2018 02:15 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Is someone killing the sessions? There's something familiar about that.
Re: No more data to read from socket [message #671951 is a reply to message #671932] Tue, 25 September 2018 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Mon, 24 September 2018 23:34
Hi Blackswan,

As you said I am unable to replicate the issue from SQL Developer .Even through the Linux also its not coming every time.Some times we are getting this problem.Don't have any clue when it is working fine and when it is giving the exception

As you suggested, asked the Java team to investigate from their side also.

Thank you very much for your interest to solve the issue.

Thanks
SaiPradyumn

If you don't know how to (re)produce the error, then how do you expect to ever fix the bug so error no longer occurs?

Again, I ask where is error 17410 documented by Oracle? Please post URL to same.

Does Java code issue SELECT directly against Oracle DB (not via calling PL/SQL procedure?
Re: No more data to read from socket [message #672045 is a reply to message #671951] Sat, 29 September 2018 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
does bug still exist?
Re: No more data to read from socket [message #672058 is a reply to message #672045] Sun, 30 September 2018 04:48 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Yes Blackswan,

Its still exist.As this issue has been intorduced after changing the existing procedure to the my new code, I am trying to re write the logic in differents ways to achieve the same.At the same time Java resource is also working from their side is there any way to fix from JDBC driver also.

Thanks for your interest to resolve the issue .

Thanks
SaiPradyumn
Re: No more data to read from socket [message #672062 is a reply to message #672058] Sun, 30 September 2018 15:49 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Sun, 30 September 2018 02:48
Yes Blackswan,

Its still exist.As this issue has been intorduced after changing the existing procedure to the my new code, I am trying to re write the logic in differents ways to achieve the same.At the same time Java resource is also working from their side is there any way to fix from JDBC driver also.

Thanks for your interest to resolve the issue .

Thanks
SaiPradyumn
ORA-17410 only exists within JDBC TTC realm
https://docs.oracle.com/database/121/JJDBC/apxermsg.htm#JJDBC28971

https://docs.oracle.com/cd/E18283_01/network.112/e10836/layers.htm#CACBJCFH
IMO, ORA-17410 can't be produced inside PL/SQL procedure; due to incompatible architecture layer.
Of course since you can't prove a negative, I can't validate my statement above.

EVERYTHING I have read & know, leads me to conclude that ORA-17410 can ONLY be thrown by JAVA code that is accessing Oracle DB via JDBC.

Please prove me wrong, if you can with reproducible test case.
Re: No more data to read from socket [message #672424 is a reply to message #672062] Mon, 15 October 2018 05:04 Go to previous message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan,


After doing some modifications to my procedure , We are able to resolve that 17410-No more data to read from socket Error.

Following is the change which we implemented

1: Removing the BULK COLLECT from the SP_SUBPARTITION_IDXS_ENABLE Sub Procedure.
We are using the CURSOR For loop to iterate the result set. But again assigning the same result set to the
collection available using the BULK COLLECT (which is already in the loop).

Just removed the BULK COLLECT INTO tPartitioned , tIndex_Name
from the following block .

PROCEDURE SP_SUBPARTITION_IDXS_ENABLE
  IS
  BEGIN
    FOR rec_indx IN
    ( SELECT DISTINCT UIS.SUBPARTITION_NAME ,
      UI.INDEX_NAME BULK COLLECT
    INTO tPartitioned ,
      tIndex_Name
    FROM USER_INDEXES UI ,
      USER_IND_PARTITIONS UIP ,
      USER_IND_SUBPARTITIONS UIS,
      USER_PART_INDEXES UPI
    WHERE UI.INDEX_NAME    = UIP.INDEX_NAME
    AND UI.INDEX_NAME      = UIS.INDEX_NAME
    AND UI.INDEX_NAME      = UPI.INDEX_NAME
    AND UI.TABLE_NAME      = UPI.TABLE_NAME
    AND UIP.PARTITION_NAME = UIS.PARTITION_NAME
    AND UI.TABLE_NAME      = v_table_name
    AND UI.TABLE_OWNER     = v_Owner_Name
    AND PARTITIONED        = 'YES'
    AND UIP.COMPOSITE      ='YES'
    AND UIS.STATUS        IN ('UNUSABLE','N/A')
    AND 1                  =
      CASE
        WHEN UPI.PARTITIONING_TYPE = 'LIST'
        AND UIP.PARTITION_NAME MEMBER OF list_partition_name
        THEN 1
        WHEN UPI.PARTITIONING_TYPE <> 'LIST'
        THEN 1
        ELSE 0
      END
    )
    LOOP
      --Rebuilding the Partitions.
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name||' REBUILD SUBPARTITION '||rec_indx.SUBPARTITION_NAME|| '  PARALLEL 16';
      --Resetting the Parallel Degree back to 1
      EXECUTE IMMEDIATE ' ALTER INDEX '||rec_indx.index_name ||' PARALLEL 1';
    END LOOP;
    IF v_Constraint_Name IS NOT NULL THEN
      EXECUTE IMMEDIATE 'ALTER TABLE '||v_table_name||' ENABLE PRIMARY KEY';
    END IF;
  END SP_SUBPARTITION_IDXS_ENABLE;  


We know that this issue we are facing when we are executing this procedure from Java and it is working fine from oracle client or SQL Developer tool, bit finally this procedure have to be executed from UI or Jobs where
JAVA invocation is mandatory.
So removed the unnecessary code from the procedure and able to solve the issue

Once again thank you so much for the support to solve the issue

Thanks
SaiPradyumn


Previous Topic: How do I know who are running the long running ops sql?
Next Topic: Is there a need to install os monitoring tools since gv$osstat already has the os statistics
Goto Forum:
  


Current Time: Wed Dec 11 16:19:35 CST 2019