oracle query - ORA-01652: unable to extend temp segment but only in some versions of sql*plus

Years ago I worked on a DR database that was fully READONLY, and even the TEMP tablespace wasn't writable. Any query that tried to spill to temp would fail (even if the temp space to be used was pretty trivial).

If this is the same situation, I wouldn't be surprised if there was a login.sql (or glogin.sql or a logon trigger) that does an ALTER SESSION to set larger PGA memory value for the session, and/or changes the optimizer goal to FIRST_ROWS.

If you can, compare the results of the following from both clients:

select * from v$parameterwhere ismodified != 'FALSE';

Also from each client for the problem SQL, try EXPLAIN PLAN FOR SELECT...and SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

See if it is coming up with different query plans.

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

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

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

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.

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

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.

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:

BEGINDECLARECURSOR 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_outerLOOP    FOR n IN query_details(m.some_primary_key)    LOOP        dbms_out.put_line(....);    END LOOP;END LOOP;END;

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

A bit more information - I've run into the same problem connecting to a different database - this time an Oracle 8i. And I can get EXPLAIN plans.

Although in this case the query completed on both clients, it took 50% longer running from Linux sql*plus 10.2.0.4.0 vs WinXP sql*plus 8.0.6.0

As I suspected, the plans are different - but both are using the default settings on the client, both are using the same optimizer mode. It seems to think the plan generated from the Linux client has a lower cost than that from the XP client (although it does take longer to run). How does the optimizer prune the search path for potential plans?