If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,495
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #4 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,393
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Posts: 727
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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 11:14.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,495
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 612
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.
Reply With Quote
  #9 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,393
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On