ORA-01652: unable to extend temp segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01652: unable to extend temp segment

  1. #1
    Join Date
    Feb 2006
    Posts
    37

    ORA-01652: unable to extend temp segment

    Any suggestion on how to resolve this issue. I have tried several options:

    1. COALESCED the tablespace
    2. Added an additional datafile
    3. Extended the maxextents to unlimited
    4. and added more space to default storage.

    Any assistance is appreciated.

    HERE's the query thats causing the error:

    select a.*
    from sys.aud$ a, ops$oracle.db_lastsid b
    where a.timestamp# between b.last_time and b.this_time;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    what's your explain plan?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Code:
    ORA-01652: unable to extend temp segment
    In what tablespace?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    Quote Originally Posted by marist89
    Code:
    ORA-01652: unable to extend temp segment
    In what tablespace?
    Jeff, are you suggesting that he is working on the wrong tablespaces??? Of course he would have already looked at the alert log top see if there are any error messages there.
    this space intentionally left blank

  5. #5
    Join Date
    Feb 2006
    Posts
    37

    ORA-01652: unable to extend temp segment

    It is the TEMP tablespace.


    Here is the plan table:

    OPERATION OPTIONS
    SELECT STATEMENT
    MERGE JOIN
    SORT JOIN
    TABLE ACCESS FULL
    FILTER
    SORT JOIN
    TABLE ACCESS FULL

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    Quote Originally Posted by CENSUSDBA
    It is the TEMP tablespace.


    Here is the plan table:

    Code:
    OPERATION	               OPTIONS
    SELECT STATEMENT	
    MERGE JOIN	
    SORT	                          JOIN
    TABLE ACCESS             	FULL
    FILTER	
    SORT	                           JOIN
    TABLE ACCESS               	FULL
    Code:
    
    
    Perhaps you should look at your disk volumes and see if any of them are nearly full, look at the max size of all of the datafiles and see if any of the datafiles are near their maxsize. This might not be an issue with the temp tablespace.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width