Thanks for the question, David.

Asked: September 14, 2017 - 8:53 am UTC

Last updated: May 27, 2022 - 5:19 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi,

I came across an "No more data to read from socket" error while trying out a codesample from here: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533477800346658909

This is our non-working environment:
SQLDeveloper 4.2.0.17.089 (Build 17.089.1709) on Windows 7 Enterprise

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

I created the table as follows:

CREATE TABLE sel_tmp_clickdata (tstamp integer, userid varchar2(15));

Then I added testdata:

INSERT INTO sel_tmp_clickdata VALUES(1, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(2, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(11, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(12, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(22, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(23, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(32, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(34, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(43, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(44, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(47, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(48, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(53, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(59, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(60, 'Sam');
INSERT INTO sel_tmp_clickdata VALUES(63, 'Mary');
INSERT INTO sel_tmp_clickdata VALUES(68, 'Sam');
commit;

There are two Query-Samples - the first one works without any hassle:

SELECT 
 tstamp,
userid
FROM sel_tmp_clickdata MATCH_RECOGNIZE(         
   ALL ROWS PER MATCH
   PATTERN (b s+)    
   DEFINE
       s as (tstamp - prev(tstamp) <= 10)

);

The second query however fails:

SELECT 
 tstamp,
userid,
session_id
FROM sel_tmp_clickdata MATCH_RECOGNIZE(         
 PARTITION BY userid ORDER BY tstamp
   MEASURES match_number() as session_id
   ALL ROWS PER MATCH
   PATTERN (b s+)
   DEFINE
       S AS (tstamp - prev(tstamp) <=10)
);

Either this query is running way too long for its content (I usually kill it after a few minutes) or it returns "No more data to read from socket" almost instantaneously.

Searching through the web lead me to trying all of the following things:
- altering the tnsnames.ora file (changing (SERVER=DEDICATED) to (SERVER=SHARED)
- using sqlplus-console instead of SQLDeveloper
- trying the statement on my local dev-db (suprisingly it worked there!)

My working environment:
same client-PC (SQLDeveloper-Version and Windows 7 Enterprise)

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

As both DBs differ in their version, this lead me to the following question(s): Is this a known bug in 12.2.0.2.0 (contrary to 12.2.0.1.0) or is there any advice you can give me on tracking down the problem?

Best regards,
david

and Connor said...

Sorry - I can't get it to fail

12.1.0.2
========

SQL> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> CREATE TABLE sel_tmp_clickdata (tstamp integer, userid varchar2(15));

Table created.

SQL>
SQL> INSERT INTO sel_tmp_clickdata VALUES(1, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(2, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(11, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(12, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(22, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(23, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(32, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(34, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(43, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(44, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(47, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(48, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(53, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(59, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(60, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(63, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(68, 'Sam');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> SELECT
  2   tstamp,
  3  userid
  4  FROM sel_tmp_clickdata MATCH_RECOGNIZE(
  5     ALL ROWS PER MATCH
  6     PATTERN (b s+)
  7     DEFINE
  8         s as (tstamp - prev(tstamp) <= 10)
  9  );

    TSTAMP USERID
---------- ---------------
         1 Mary
         2 Sam
        11 Mary
        12 Sam
        22 Sam
        23 Mary
        32 Sam
        34 Mary
        43 Sam
        44 Mary
        47 Sam
        48 Sam
        53 Mary
        59 Sam
        60 Sam
        63 Mary
        68 Sam

17 rows selected.

SQL>
SQL>
SQL> SELECT
  2   tstamp,
  3  userid,
  4  session_id
  5  FROM sel_tmp_clickdata MATCH_RECOGNIZE(
  6   PARTITION BY userid ORDER BY tstamp
  7     MEASURES match_number() as session_id
  8     ALL ROWS PER MATCH
  9     PATTERN (b s+)
 10     DEFINE
 11         S AS (tstamp - prev(tstamp) <=10)
 12  );

    TSTAMP USERID          SESSION_ID
---------- --------------- ----------
         1 Mary                     1
        11 Mary                     1
        34 Mary                     2
        44 Mary                     2
        53 Mary                     2
        63 Mary                     2
         2 Sam                      1
        12 Sam                      1
        22 Sam                      1
        32 Sam                      1
        43 Sam                      2
        47 Sam                      2
        48 Sam                      2
        59 Sam                      3
        60 Sam                      3
        68 Sam                      3

16 rows selected.

SQL>

12.2.0.1
========

SQL> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

5 rows selected.

SQL>
SQL> CREATE TABLE sel_tmp_clickdata (tstamp integer, userid varchar2(15));

Table created.

SQL>
SQL> INSERT INTO sel_tmp_clickdata VALUES(1, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(2, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(11, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(12, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(22, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(23, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(32, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(34, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(43, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(44, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(47, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(48, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(53, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(59, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(60, 'Sam');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(63, 'Mary');

1 row created.

SQL> INSERT INTO sel_tmp_clickdata VALUES(68, 'Sam');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> SELECT
  2   tstamp,
  3  userid
  4  FROM sel_tmp_clickdata MATCH_RECOGNIZE(
  5     ALL ROWS PER MATCH
  6     PATTERN (b s+)
  7     DEFINE
  8         s as (tstamp - prev(tstamp) <= 10)
  9  );

    TSTAMP USERID
---------- ---------------
         1 Mary
         2 Sam
        11 Mary
        12 Sam
        22 Sam
        23 Mary
        32 Sam
        34 Mary
        43 Sam
        44 Mary
        47 Sam
        48 Sam
        53 Mary
        59 Sam
        60 Sam
        63 Mary
        68 Sam

17 rows selected.

SQL>
SQL>
SQL> SELECT
  2   tstamp,
  3  userid,
  4  session_id
  5  FROM sel_tmp_clickdata MATCH_RECOGNIZE(
  6   PARTITION BY userid ORDER BY tstamp
  7     MEASURES match_number() as session_id
  8     ALL ROWS PER MATCH
  9     PATTERN (b s+)
 10     DEFINE
 11         S AS (tstamp - prev(tstamp) <=10)
 12  );

    TSTAMP USERID          SESSION_ID
---------- --------------- ----------
         1 Mary                     1
        11 Mary                     1
        34 Mary                     2
        44 Mary                     2
        53 Mary                     2
        63 Mary                     2
         2 Sam                      1
        12 Sam                      1
        22 Sam                      1
        32 Sam                      1
        43 Sam                      2
        47 Sam                      2
        48 Sam                      2
        59 Sam                      3
        60 Sam                      3
        68 Sam                      3

16 rows selected.

SQL>

I also tried with SQL Developer 17.2.0.188 and it worked fine there too.

So I'd suggest

1) see if it works in plain SQL Plus
2) try the more recent SQL Developer version

It all else fails, perhaps try take it up with Support.

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

select * from v$pdbs; -- throwing connection time out error

A reader, May 31, 2019 - 1:38 pm UTC

No more data to read from socket

Srinivas, May 31, 2019 - 1:44 pm UTC

I could see "No more data to read from socket" error if i run the below statement.

select * from v$pdbs;

Where as select name,open_mode from v$pdbs; working fine.

Please let me know what might be the reason.

June 03, 2019 - 8:48 am UTC

I suspect a data issue. Start with this (this is from 18c)

select
 CON_ID
,DBID
,CON_UID
,GUID
,NAME
,OPEN_MODE
,RESTRICTED
,OPEN_TIME
,CREATE_SCN
,TOTAL_SIZE
,BLOCK_SIZE
,RECOVERY_STATUS
,SNAPSHOT_PARENT_CON_ID
,APPLICATION_ROOT
,APPLICATION_PDB
,APPLICATION_SEED
,APPLICATION_ROOT_CON_ID
,APPLICATION_ROOT_CLONE
,PROXY_PDB
,LOCAL_UNDO
,UNDO_SCN
,UNDO_TIMESTAMP
,CREATION_TIME
,DIAGNOSTICS_SIZE
,PDB_COUNT
,AUDIT_FILES_SIZE
,MAX_SIZE
,MAX_DIAGNOSTICS_SIZE
,MAX_AUDIT_SIZE
,LAST_CHANGED_BY
,TEMPLATE
,TENANT_ID
,UPGRADE_LEVEL
,GUID_BASE64
from v$pdbs;

and comment in/out columns to see if that narrows it down to an offending column

Srinivas Pythari, June 03, 2019 - 9:04 am UTC

Thanks for the quick response Tom.

GUID_BASE64 column creating the problem and if the column included in the select statement, oracle throwing "No more data to read from socket" error.

How selecting a column throwing "No more data to read from socket".

Thanks in advance.

Srinivas, June 03, 2019 - 9:28 am UTC

Thanks for the quick response Connor McDonald.

GUID_BASE64 column creating the problem and if the column included in the select statement, oracle throwing "No more data to read from socket" error.

How selecting a column throwing "No more data to read from socket".

Thanks in advance.

June 05, 2019 - 12:14 am UTC

Thats normally indicative of a bug. Check the alert log to see if its hiding a lower level error (often you'll see an ora-600 or ora-7445 that is what actually caused the "no more data").

I think you'll need to speak to Support. Until then...don't include that column :-)

Traces for "No more data to read from socket"

HUANG Sheng, June 28, 2019 - 3:28 pm UTC

I've got the error "SQLException 17410 No more data from socket" in my application server log.
I've checked the alert.log but i found nothing at the time this occurs.
Is there any log files i may check ? Is there any option to get more log information ?
Does it mean that the error is not related to database but the JDBC driver ?

July 02, 2019 - 4:30 am UTC

Unfortunately this is a fairly generic error. It simply means your client program reached out to the database via the network and got told the database connection you were using is no longer present.

So it could be

- network issue (connectivity/firewall etc)
- software bug in the driver
- db has restarted/failed over and the client does not handle it

I think you'll need to get the entire java stack and log a support call. Lots of *potential* causes here.

Also check MOS note 2301162.1.

SOCKET ERROR

A reader, February 10, 2022 - 1:50 pm UTC

If you are joining CTE and if same value you r showing in two columns then you might face this problem. so do not use same value in 2 column in cte becsz oracle doesnt hv control on CTE becz it is memory based not table. Aftger removing one col from cte it worked for me.

Same error with group by clause

Mansi, May 27, 2022 - 1:54 pm UTC

I am getting no more data to read from socket with group by clause.
When I remove group by and just keep the select columns, the query works well.
P.s. we migrated from 12c to 19c. and this was not a problem earlier in 12c.

Any suggestion on this.

May 27, 2022 - 5:19 pm UTC

As Connor's said, this is a generic error; there are lots of things that could be causing this.

Put together a test case showing the problem, post it here and reach out to support.

[SOLVED] by correcting the correct SID

Rakesh Babu, October 26, 2022 - 10:15 am UTC

I also faced this error of "No more data to read from socket".

after correcting the spelling that I supplied in Oracle system ID (SID) parameter in the connection string RESOLVED the error.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

select * from v$pdbs; -- throwing connection time out error

No more data to read from socket

Traces for "No more data to read from socket"

SOCKET ERROR

Same error with group by clause

[SOLVED] by correcting the correct SID

More to Explore

Analytics

No more data to read from socket (12.1.0.1.0 vs 12.1.0.2.0)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

select * from v$pdbs; -- throwing connection time out error

No more data to read from socket

Traces for "No more data to read from socket"

SOCKET ERROR

Same error with group by clause

[SOLVED] by correcting the correct SID

More to Explore

Analytics