none
ORA-01652: unable to extend temp segment

    Question

  • I know this is an Oracle error message, but bear with me. I moved my SQL server installation from one server to another this weekend. The new server is the exact same configuration, or as close as humanly possible. They are the same version of windows, same version / SP level of SQL server (2008 R2 Standard), they have the same version of Oracle installed, and the same TNSNAMES entry and linked server configuration for the oracle server we are trying to query. Running my query on the old server returns the expected results, while the exact same query on the new server returns

    OLE DB provider "OraOLEDB.Oracle" for linked server "LinkedServerName" returned message "ORA-01652: unable to extend temp segment by 368 in tablespace SYSTEM

    ORA-02063: preceding line from HACC_LINK".

    I've reached out to the support team for that oracle server. However, a) it's a 3rd party software vendor, so I'm not too confident that I can get them to make any changes on their side, and b) they are aware of the recent server upgrade so I'm sure they're going to say it's something with my SQL aserver . I can't say I blame them.

    Any idea what could be different on the SQL side that woudl cause this?

    Monday, February 03, 2014 9:12 PM

Answers

  • This morning it is working fine. The plan cache makes sense, but at the same time, this particular query has been in use for years, and has never caused this problem. I guess I was hoping there was a simple explanation - "you forgot to enable _____". No such luck here I suppose. Thanks everyone for your help.

    Eric

    Tuesday, February 04, 2014 1:51 PM

All replies

  • Can you share your query ?

    Can you you whether this thread is related to your issue?

    http://s108.codeinspot.com/q/378831

    Monday, February 03, 2014 9:28 PM
    Moderator
  • That thread didn't appear to be particularly related.

     

    I don't think the specific query that I'm running will help. It's very long, and mostly proprietary stuff that wouldnt make much sense unless you happen to work for this particular vendor. Plus, the identical query works on the old server, so it is definitely ok as-is.

    Monday, February 03, 2014 9:46 PM
  • You need to extend the temp tablespace either you can add a file or re size the existing one.

    ALTER DATABASE TEMPFILE 'E:\oracle\oradata\Dssp1\TEMP3.DBF' RESIZE 100M;
    ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE\ORADATA\test\TEMP2.DBF'  SIZE 4000M;

    http://www.dba-oracle.com/sf_ora_01652_unable_to_extend_temp_segment_by_string_in_tablespace_string.htm

    --Prashanth


    Monday, February 03, 2014 9:55 PM
  • I'll try to convince the Oracle DBAs of this. However, any idea why I can run the same query from an identical SQL server without needing to do this?

    Monday, February 03, 2014 10:03 PM
  • Obviously we are completely in the dark. But since you restarted SQL Server, your plan cache was cleared, and the the query plan was changed, leading to a plan that provokes this problem on the Oracle side. If that is the case, I would say that it was an accident waiting to happen - and now it happened.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 03, 2014 11:19 PM
  • This morning it is working fine. The plan cache makes sense, but at the same time, this particular query has been in use for years, and has never caused this problem. I guess I was hoping there was a simple explanation - "you forgot to enable _____". No such luck here I suppose. Thanks everyone for your help.

    Eric

    Tuesday, February 04, 2014 1:51 PM
  • Yes, it is always a surprise when a query that always have worked, suddenly goes south.

    Of course, in this particular day, it could simply be that the Oracle server was having a bad day. One of life's mysteries...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 04, 2014 2:59 PM