Oracle 不再需要从套接字读取数据 (12.1.0.1.0 vs 12.1.0.2.0)

嗨，

从这里尝试代码示例时，我遇到了 “没有更多数据要从套接字读取” 错误:https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533477800346658909

这是我们的非工作环境:
Windows 7企业版上的SQLDeveloper 4.2.0.17.089 (构建17.089.1709)

Oracle数据库12c企业版版本12.1.0.2.0-64位生产
PL/SQL版本12.1.0.2.0-生产
“核心12.1.0.2.0生产”
适用于Linux的TNS: 版本12.1.0.2.0-生产
NLSRTL版本12.1.0.2.0-生产


我创建的表格如下:

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

然后我添加了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;

有两个查询样本-第一个工作没有任何麻烦:

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

);

但是第二个查询失败:

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)
);

该查询的内容运行时间太长 (我通常会在几分钟后将其杀死)，或者它几乎立即返回 “没有更多数据可从套接字读取”。

通过网络搜索导致我尝试以下所有事情:
-更改tnsnames.ora文件 (将 (服务器 = 专用) 更改为 (服务器 = 共享)
-使用sqlplus-console而不是SQLDeveloper
-在我的本地dev-db上尝试该语句 (令人惊讶的是，它在那里工作!)

我的工作环境:
相同的客户端-PC (SQLDeveloper-版本和Windows 7企业版)

Oracle数据库12c企业版版本12.2.0.1.0-64位生产
PL/SQL版本12.2.0.1.0-生产
“核心12.2.0.1.0生产”
适用于Linux的TNS: 版本12.2.0.1.0-生产
NLSRTL版本12.2.0.1.0-生产

由于两个数据库的版本不同，这导致我以下问题: 这是12.2.0.2.0中的一个已知错误 (与12.2.0.1.0相反)，还是你可以给我任何关于跟踪问题的建议？

致以最诚挚的问候，
大卫

抱歉-我不能让它失败

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>

我还尝试了SQL Developer 17.2.0.188，它在那里也可以正常工作。

所以我建议

1) 看看它是否适用于纯SQL Plus
2) 尝试更新的SQL Developer版本

其他所有方法都失败了，也许尝试在支持下进行。