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.

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

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.
The nice thing about being senile is you can hide your own Easter eggs.

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.
The nice thing about being senile is you can hide your own Easter eggs.

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.

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.

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.

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.