oracle查询-ORA-01652:无法扩展临时段，但仅在某些版本的sql * plus中 [英] oracle query - ORA-01652: unable to extend temp segment but only in some versions of sql*plus

问题描述

这个让我颇为困惑.我编写了一个查询，该查询在我的开发客户端上运行良好，但在生产客户端上失败，错误为"ORA-01652:无法通过....扩展临时段.".在这两种情况下，数据库和用户都是相同的.在我的开发机器(MS Windows)上，我有SQL * PLUS(版本9.0.1.4.0)和Toad 9.0(都使用oci.dll的9.0.4.0.1版本).两者都运行代码而没有错误.

This one has me rather confused. I've written a query which runs fine from my development client but fails on the production client with error "ORA-01652: unable to extend temp segment by....". In both instances, the database and user is the same. On my development machine (MS Windows) I've got SQL*PLUS (Release 9.0.1.4.0) and Toad 9.0 (both using version 9.0.4.0.1 of the oci.dll). Both run the code without errors.

但是，当我使用同一台用户名/密码从另一台计算机运行同一文件，针对同一数据库时，这次是10.2.0.4.0版(来自10.2.0.4-1 Oracle Instant Client)，我得到了错误.

However when I run the same file, against the same database, using the same username/password from a different machine, this time version 10.2.0.4.0 (from the 10.2.0.4-1 Oracle instant client) I get the error.

它确实是可重复发生的.

It does occur reproducibly.

不幸的是，我对数据库中设置为只读的字典视图的访问权限有限(甚至无法获得解释计划！).

Unfortunately I've only got limited access to the dictionary views on the database which is set up as read-only (can't even get an explain plan!).

我已经尝试通过调整查询来解决此问题(我怀疑存在较大的临时结果集，随后对该结果集进行了精简)，但没有设法更改任一客户端的行为.

I've tried working around the problem by tuning the query (I suspect that there is a large interim result set which is subsequently trimmed down) but have not managed to change the behaviour at either client.

可能会在导致问题的计算机上部署不同版本的客户端-但目前看来已降级为以前的版本.

It may be possible to deploy a different version of the client on the machine causing the problems - but currently that looks like downgrading to a previous version.

有什么想法吗?

TIA

基于以下Gary的回答，我看了看glogin.sql脚本-唯一的区别是工作客户端上存在'SET SQLPLUSCOMPATIBILITY 8.1.7'，但客户端失败时却不存在-但添加时没有解决问题.

Based on Gary's answer below, I had a look at the glogin.sql scripts - the only difference was that 'SET SQLPLUSCOMPATIBILITY 8.1.7' was present on the working client but absent on failing client - but adding it in did not resolve the problem.

我也尝试过

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

和

alter session set sort_area_size=1048576000;

无济于事:(

我设法找到了相同的行为，这次是与Oracle 8i后端通信的.在这种情况下，数据库为RW.这使我得以确认，正如我所怀疑的那样，不同的客户正在制定不同的计划.但是为什么???

I managed to find the same behaviour, this time talking to an Oracle 8i backend. In this case the database was RW. That allowed me to confirm that the different clients were, as I suspected, generating different plans. But why????

两个客户都在查看"SHOW PARAMETERS"的输出时报告了完全相同的设置！

Looking at the output of 'SHOW PARAMETERS' both clients reported exactly the same settings!

推荐答案

这并不是真正的答案-而是更多信息....

Not really an answer - but a bit more information....

我们的本地DBA能够确认确实使用了16Gb(！)TEMP表空间并已将其填满，但这仅是从Linux客户端获取的(我能够通过从PHP进行oci8调用来重新创建错误).在sqlplus客户端的情况下，我实际上是使用完全相同的文件在两个客户端上运行查询(通过scp复制而不进行文本转换-因此行尾为CRLF-即字节对字节与Windows客户端上运行的字节相同)

Our local DBAs were able to confirm that the 16Gb (!) TEMP tablespace was indeed being used and had filled up, but only from the Linux clients (I was able to recreate the error making an oci8 call from PHP). In the case of the sqlplus client I was actually using exactly the same file to run the query on both clients (copied via scp without text conversion - so line endings were CRLF - i.e. byte for byte the same as was running on the Windows client).

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

So the only rational solution was that the 2 client stacks were resulting in different execution plans!

在负载非常少的DBMS上同时在两个客户端上运行查询会得到相同的结果-这意味着两个客户端也会为查询生成不同的sqlids.

Running the query from both clients approx simultaeneously on a DBMS with very little load gave the same result - meaning that the two clients also generated different sqlids for the query.

(Oracle也忽略了我的提示-我讨厌这样做).

(and also Oracle was ignoring my hints - I hate when it does that).

Oracle绝不应该这样做-即使它在将查询提交给DBMS之前进行了一些内部查询(这会引起不同的sqlid)，但所使用的客户端堆栈应该完全透明.选择执行计划-仅应根据查询的内容和DBMS的状态进行更改.

There is no way Oracle should be doing this - even if it were doing some internal munging of the query before presenting it to the DBMS (which would give rise to the different sqlids) the client stack used should be totally transparent regarding the choice of an execution plan - this should only ever change based on the content of the query and the state of the DBMS.

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

The problem was complicated by not being to see any explain plans - but for the query to use up so much temporary tablespace, it had to be doing a very ugly join (at least partially cartesian) before filtering the resultset. Adding hints to override this had no effect. So I resolved the problem by splitting the query into 2 cursors and doing a nested lookup using PL/SQL. A very ugly solution, but it solved my immediate problem. Fortunately I just need to generate a text file.

为了任何人在类似的酱菜中发现自己的利益:

For the benefit of anyone finding themselves in a similar pickle:

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，就越讨厌它！

The more I use Oracle, the more I hate it!

这篇关于oracle查询-ORA-01652:无法扩展临时段，但仅在某些版本的sql * plus中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！