Edi Yanto (何 萬 新)

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Posted by: Edi Yanto on: April 19, 2009

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

MetaLink has a very detailed and informative article concerning ORA-01652 and RAC.  There is some troubleshooting required with ORA-01652 in RAC because there are two common causes in this area.

  1. ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.
  2. ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances.

To troubleshoot for ORA-01652,  and find out which of the above scenarios are causing ORA-01652 use this query offered by MetaLink:

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = ‘<TEMP TABLESPACE NAME>’

You will know that the first scenario is causing ORA-01652 to be thrown if the free block reads ‘0′ because it signifies that there is no free space.

If there is a good amount of space, you know that there is another cause for ORA-01652, and it is probably the second scenario.  It is important to note that in a non-RAC environment, local instances are not able to extend the temp segments.

In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:

  1. Increase size of the temp tablespace
  2. Increase sort_area_size and/or pga_aggregate_target

If ORA-01652 is causing the slowdown, SMON will probably not be able to process the sort segment requests, you  should try to diagnose the contention:

  • Output from the following query periodically during the problem:
    select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
    from gv$sort_segment;
  • Global hang analyze and systemstate dumps

Reference: Oracle Error Tips by Burleson Consulting

Leave a Reply

RSS Syndication


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn
Edi Yanto (何 萬 新) - Blogged Page Rank Check

My Certifications


  • Edi Yanto: Hi Ricky, Have you try to set the security setting (Enable Automatic prompting for file download) for your internet/local/trusted sites zone at inter
  • Ricky: how can i view the report in oracle by browser or by postcript viewer, when i clack report suddenly the menu popup then it will close, but when i'm pr
  • Edi Yanto: @boy, do you know if u change this code to v_name varchar2(255), you'll recode it, if any change to the length of the last_name column in employees t

RSS Edi Yanto (何万新)

  • Oracle Top-N Query October 2, 2009
    Using RANK function, with a faster execution plan (WINDOW SORT PUSHED RANK) SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, RANK() OVER (ORDER BY SAL Desc) AS Emp_Rank FROM Emp) WHERE Emp_Rank
    Edi Yanto
  • Bersyukurlah… June 30, 2009
    AKU TAK SELALU MENDAPATKAN APA YANG KUSUKAI oleh karena itu AKU SELALU MENYUKAI APAPUN YANG AKU DAPATKAN. Kata-kata di atas merupakan wujud syukur. Syukur merupakan kualitas hati yang terpenting. Dengan bersyukur kita akan senantiasa diliputi rasa damai, tentram dan bahagia. Sebaliknya, perasaan tak bersyukur akan senantiasa membebani kita. Kita akan selalu […]
    Edi Yanto
  • Cinta Sejati June 30, 2009
    Kenapa kita menutup mata ketika kita tidur ? Kenapa kita menutup mata ketika kita menangis ? Kenapa kita menutup mata ketika kita membayangkan sesuatu ? Kenapa kita menutup mata ketika kita berciuman ? Hal hal yang terindah di dunia ini biasanya tidak terlihat Ada hal hal yang tidak ingin kita lepaskan dan ada orang orang yang tidak ingin kita tinggalkan Tap […]
    Edi Yanto
  • Nice Story June 8, 2009
    Share on Facebook
    Edi Yanto
  • 5 Tips Agar Tak Mudah Lelah June 2, 2009
    Sebagian orang mengeluhkan kondisi tubuhnya yang cepat capai atau lelah. Kebanyakan orang menilai hal itu diakibatkan kerja ekstra. Tetapi, sebenarnya hal itu dilatarbelakangi kebiasaan seseorang sehari-hari. Berikut lima tips yang dikutip VIVAnews dari WebMD, Sabtu 25 April 2009 agar kondisi tubuh Anda tidak mudah capai atau lelah. 1. Lapar atau tidak, sara […]
    Edi Yanto
  • Belajar dari Alphabet May 29, 2009
    A : AcceptTerimalah diri anda sebagaimana adanya. B : BelievePercayalah terhadap kemampuan anda untuk meraih apa yang anda inginkan dalam hidup. C : CarePedulilah pada kemampuan anda meraih apa yang anda inginkan dalam hidup. D : DirectArahkan pikiran pada hal-hal positif yang meningkatkan kepercayaan diri. E : EarnTerimalah penghargaan yang diberi orang lai […]
    Edi Yanto
  • How To Get the List of All Responsibilities Assigned To A User May 29, 2009
    Edi Yanto
  • Query To Find Multi-Org Is Enabled Or Not May 29, 2009
    SELECT DECODE (multi_org_flag, ‘Y’, ‘Multi-Org Enabled’, ‘Multi-Org Disabled’ ) FROM fnd_product_groups; Share on Facebook
    Edi Yanto
  • How To Find A Patch Is Applied For A Bug May 29, 2009
    Edi Yanto
  • Excel Limitations on Images in Oracle Apps May 23, 2009
    The Excel output can’t showing the image (even after you save and send to another person or open it at different notebook), but it is showing only small red color empty cross written ‘An Image’.  Why??? As many of you know or have noticed, our Excel output for all flavors of BIP/XMLP is not true binary [...]
    Edi Yanto

 

April 2009
M T W T F S S
« Mar    
 12345
6789101112
13141516171819
20212223242526
27282930  

Visitors

Blog Stats

  • 27,727 hits

Users Online

web counter

Pages