oracle 查询 - ORA-01652: 无法扩展临时段，但仅限于某些版本的 sql*plus

Question

这一点让我相当困惑。我编写了一个查询，该查询在我的开发客户端上运行良好，但在生产客户端上失败，并出现错误“ORA-01652：无法扩展临时段......”。在这两种情况下，数据库和用户是相同的。在我的开发机器 (MS Windows) 上，我安装了 SQL*PLUS（版本 9.0.1.4.0）和 Toad 9.0（均使用 oci.dll 版本 9.0.4.0.1）。两者都运行代码没有错误。

但是，当我在不同的计算机上使用相同的用户名/密码针对同一个数据库运行同一个文件时，这次版本 10.2.0.4.0（来自 10.2.0.4-1 Oracle 即时客户端）出现错误。

它确实会重复发生。

不幸的是，我对数据库上的字典视图的访问权限有限，该数据库设置为只读（甚至无法获得解释计划！）。

我尝试通过调整查询来解决这个问题（我怀疑有一个很大的临时结果集，随后被修剪），但没有设法改变任一客户端的行为。

可能会在导致问题的计算机上部署不同版本的客户端 - 但目前看起来像是降级到以前的版本。

有什么想法吗？

TIA

更新

根据下面加里的回答，我查看了 glogin.sql 脚本 - 唯一的区别是“SET SQLPLUSCOMPATIBILITY 8.1.7”存在于工作客户端上，但在失败客户端上不存在 - 但添加它并没有解决问题。

我也尝试过

alter session set workarea_size_policy=manual;
alter session set hash_area_size=1048576000;

，但

alter session set sort_area_size=1048576000;

没有成功:(

更新 2

我设法找到了相同的行为，这次是与 Oracle 8i 后端对话。在这种情况下，数据库是 RW。这使我能够确认不同的客户端是，正如我怀疑的那样，但为什么？？？

查看“显示参数”的输出，两个客户端都报告了完全相同的设置！

Answer 1

几年前，我开发了一个完全只读的 DR 数据库，甚至 TEMP 表空间也不可写。任何尝试溢出到临时空间的查询都会失败（即使要使用的临时空间非常小）。

如果是相同的情况，如果有一个 login.sql （或 glogin.sql 或登录触发器）执行 ALTER SESSION 来为会话设置更大的 PGA 内存值，和/或更改优化器目标为 FIRST_ROWS。

如果可以，请比较两个客户端的以下结果：

select * from v$parameter
其中 ismodified!= 'FALSE';

另外，对于每个客户端的问题 SQL，请尝试 EXPLAIN PLAN FOR SELECT...
和 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

看看它是否提出了不同的查询计划。

Answer 2

不是真正的答案 - 但更多信息......

我们本地的 DBA 能够确认 16Gb (!) TEMP 表空间确实正在使用并已填满，但仅限于 Linux 客户端（我能够重新创建从 PHP 进行 oci8 调用时出错）。在 sqlplus 客户端的情况下，我实际上使用完全相同的文件在两个客户端上运行查询（通过 scp 复制，无需文本转换 - 所以行结尾是 CRLF - 即逐字节与在 Windows 客户端上运行的相同） 。

因此，唯一合理的解决方案是两个客户端堆栈导致不同的执行计划！

在负载非常小的 DBMS 上大约同时从两个客户端运行查询会得到相同的结果 - 这意味着两个客户端还为查询生成不同的 sqlid。

（而且 Oracle 也忽略了我的提示 - 我讨厌它这样做）。

Oracle 不应该这样做 - 即使它在将查询呈现给 DBMS 之前对其进行了一些内部修改（这将产生不同的 sqlid），所使用的客户端堆栈对于选择一个查询应该是完全透明的。执行计划 - 这应该只根据查询的内容和 DBMS 的状态而改变。

由于没有看到任何解释计划，问题变得很复杂 - 但是对于要使用这么多临时表空间的查询，它必须在过滤结果集之前执行非常丑陋的连接（至少部分是笛卡尔连接）。添加提示来覆盖它没有效果。因此，我通过将查询拆分为 2 个游标并使用 PL/SQL 进行嵌套查找来解决该问题。这是一个非常丑陋的解决方案，但它解决了我眼前的问题。幸运的是我只需要生成一个文本文件。

为了那些陷入类似困境的人的利益：

BEGIN

DECLARE
CURSOR query_outer IS
    SELECT some_primary_key, some_other_stuff
    FROM atable
    WHERE....

CURSOR query_details (p_some_pk) IS
    SELECT COUNT(*), SUM(avalue)
    FROM btable
    WHERE fk=p_some_pk
    AND....

FOR m IN query_outer
LOOP
    FOR n IN query_details(m.some_primary_key)
    LOOP
        dbms_out.put_line(....);
    END LOOP;
END LOOP;

END;

我使用 Oracle 的次数越多，我就越讨厌它！

Answer 3

更多信息 - 我在连接到不同的数据库时遇到了同样的问题 - 这次是 Oracle 8i。我可以获得解释计划。

虽然在这种情况下，查询在两个客户端上完成，但从 Linux sql*plus 10.2.0.4.0 与 WinXP sql*plus 8.0.6.0 运行的时间要长 50%

正如我怀疑的那样，计划是不同的 - 但两者都使用默认值客户端上的设置，两者都使用相同的优化器模式。似乎认为从 Linux 客户端生成的计划比从 XP 客户端生成的计划成本更低（尽管运行时间确实更长）。优化器如何修剪潜在计划的搜索路径？