Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    1

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

    Hi to all,
    today we faced with ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP. I queried psamptemp and saw that it is 100% full. should i add a new file to the tablespace or there is some wrong parameter configuration? As I know If the PGA is not sufficient, the operation uses the temporary permanent storage (PSAPTEMP). How can I check if that is the case so i can correct the parameter?
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,658
    Post Operating System (OS) name & version for DB server system.
    Post results of SELECT * from v$version.

    Code:
    01652, 00000, "unable to extend temp segment by %s in tablespace %s"
    // *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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    add another tempfile to the temp tablespace
    or
    correct/tune the code that is doing so much sorting
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,435
    If the problem had not occurred before, the problem is with a new select. find out what it is and rewrite or index for the query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2004
    Posts
    746
    As others have pointed out, there are two ways to fix your problem - if you can't use either of them then the problem can't be fixed.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Nov 2009
    Posts
    1
    Hi All....

    Evn i am facing the same problem...

    ORA-12801: error signaled in parallel query server P000
    ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

    The above error occured while creating the temporary table using the select statement

    I removed the parallel hints,used non parallel hints and tried,but still the same issue..

    Can anyone tell which hints should be added since it's doing full table scan on two big tables,also 4/5 nested loops ...?
    Last edited by Suja_SCT; 11-17-09 at 12:14.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,658
    I seriously doubt any HINT will avoid this error.

    Add another datafile to this tablespace
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  8. #8
    Join Date
    Mar 2007
    Posts
    615
    Quote Originally Posted by Suja_SCT View Post
    Can anyone tell which hints should be added since it's doing full table scan on two big tables,also 4/5 nested loops ...?
    Did you try RUN_FASTER?
    Seriously, if there would be any hint which would improve performance (or at least keep it the same) for 100% queries, why would it not be implemented by Oracle as the default optimizer behaviour?
    As you did not post any details about that troubled query, the answer provided by The_Duck is possibly the best you can get. If you are not using very old Oracle version (you also did not post), you may check whether statistics on all involved tables are up-to-date and correct.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,435
    Ask your DBA (nicely) Why he wont get off his butt and extend the table-space. That is his job. If he says it is already over-sized and wont do it and you are using a code generator to generate the query, then you are just SOL.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    i am affraid this is not the issue of temporary tablespace altogather .

    That error occurs when failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
    So we can this in two ways.



    1.
    Check the datafiles sizes..
    SELECT * FROM DBA_DATA_FILES;

    FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    1 D:\ORACLEXE\ORADATA\XE\USERS.DBF 4 USERS 104857600 12800 AVAILABLE
    2 D:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 3 SYSAUX 450887680 55040 AVAILABLE
    3 D:\ORACLEXE\ORADATA\XE\UNDO.DBF 2 UNDO 94371840 11520 AVAILABLE
    4 D:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 1 SYSTEM 629145600 76800 AVAILABLE

    Then resize your datafile or add a new datafile to current tablespace
    Resizing Datafile :
    ALTER DATABASE DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS.DBF‘ RESIZE 200M;
    Addind Datafile to existing Tablespace:
    ALTER TABLESPACE USERS ADD DATAFILE ‘D:\ORACLEXE\ORADATA\XE\USERS_2.DBF‘ SIZE 50M;



    2.
    Change the user’s default tablespace to a bigger one
    SELECT * FROM Dba_Users;

    USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
    1 ASD 36 OPEN SYSTEM
    2 SYS 0 OPEN SYSTEM
    3 SYSTEM 5 OPEN SYSTEM
    4 ANONYMOUS 28 OPEN SYSAUX

    ALTER USER asd DEFAULT TABLESPACE users;

    It changes “asd” schema’s default tablespace to USERS tablespace.
    SELECT * FROM Dba_Users;

    USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESPACE
    1 ASD 36 OPEN USERS
    2 SYS 0 OPEN SYSTEM
    3 SYSTEM 5 OPEN SYSTEM
    4 ANONYMOUS 28 OPEN SYSAUX

    You can also do this per object : “alter table table_name move tablespace users; ”

    You can also select the tablespace in the create script of the object.

  11. #11
    Join Date
    Aug 2009
    Posts
    262
    And for ASk TOM WEBSITE

    -- Thanks for the question regarding "strange ORA-01652 error", version 9.0.1
    Submitted on 5-Mar-2003 12:37 Central time zone
    Tom's latest followup | Bookmark | Bottom
    Last updated 14-Jan-2008 15:32
    You Asked

    Hi, Tom,

    I got this error message when I tried to rebuild an index.
    ORA-01652: unable to extend temp segment by 1024 in tablespace INDX
    I have INDX tablespace to hold all indexes. INDX tablespace
    is LMT, PERMENT, LOGGING.
    I also have TEMP tablespace as default temporary tablespace.
    I don't understand why Oracle is trying to extend temp segment on
    a perment tablespace INDEX instead of TEMP where the sorting happens?

    Thanks.


    and we said...

    Oracle will put the newly rebuilt index structure into a TEMP segment in the tablespace
    where the index is going to go. This is so that if the system just crashed right in the
    middle, when we restart -- SMON will be kind enough to seek out these orphan TEMP
    segments and get rid of them.

    Upon completion of the index rebuild -- Oracle will convert this TEMP segment into a
    permanent segment, remove the old index segment and let this one take over.

    So, this TEMP segment is really your newly rebuilt index as it is rebuilding.

    You did not have enough space for both the old and new index in that tablespace is all.


    Reviews
    4 stars Excellent !! January 12, 2008 - 8pm Central time zone
    Bookmark | Bottom | Top
    Reviewer: Raghu

    Thomas, this solved my puzzle too. We had similar problems on 10202 during the usage of
    DBMS_REDEFINITION package. I overlooked at the entire error in the alert log and when isaw that it
    was unable to increase the TEMP segment, i thought we need more space on the TEMP tablespace. But
    we had failure again. I did online monitoring of the SORT_SEGMENT usage and it never hit the max
    limit and i was wondering what's the real issue. After reading this thread i relooked at the alert
    log and found that it was not the problem with the TEMP tablespace but with the INDEX tablespace
    itself. So i have few clarifications to get my concepts clear.

    -- so this the case only incase of rebuilt ?
    -- in case of new index creation/primary key creation, should i size the TEMP tablesapce to hold
    the sort data or the index tablespace ? I think its still the TEMP tablespace right ?

    thanks in advance.

    Raghu



    Followup January 14, 2008 - 3pm Central time zone:
    this is the case of new segments being created.

    a create table as select - or create index would do this.

    You size temp for temporary table used to create the segment
    the tablespace for the segment must be sized to hold the segment


    basically, until the new segment is complete - we use "temporary extents" to hold it - so that a) you cannot SEE IT (it is not done) and b) if something fails, it just gets cleaned up naturally without any special work on our part.

Posting Permissions

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