-
I have three temporaray tablespaces with 10 GB each .
Recently , When we run some query we are getting unable to
extent temp segment (ORA 1652 ) . We were running same query for last three months without any problems.
These queries used to take 20 minutes, and they are now taking 90 to 180 minutes to complete.
However, what I don't understand is how a query on 300Mb of data, which is summarizing statistcs on 6000-10000 distinct groups and producing a 20 Mb summary can use over 10GB of temporary table space.
Could anyone have any idea ?
-
Check to see if Oracle is performing sorts in the ts; these can be cleaned out by bouncing the db. Consider setting a larger extent size in the temp ts (if small), and turn on Autoextend on the ts.
-
What is the version of Oracle?
Of what type is this particular temp tablespace - TEMPORARY or PERMANENT? What are tablespace's storage parameters INITIAL, NEXT, PCTINCREASE? If it is of tipe permanent, are you sure you have no permanent segments (like tables, indexes) accidentally inthere? If PCTINCREASE is not 0, are there many free_space chuncks of different sizes there?
ORA 1652 also reports the number of blocks it was trying to grab for the new extent - what was the number reported (specify also your db_block_size).
-
check if the TEMP tablespace is assigned for this user, otherwise will use SYSTEM tablespace which could be the cause for this problem.
-
Check the pctincrease parameter,contents for temporary tablespace. Make sure contents are TEMPORARY and PCTINCREASE not set to zero, allowing SMON to coalesce free space on thats left on TEMP.
[Edited by sreddy on 01-08-2001 at 06:18 PM]
-
check the next_extent from dba_segments view as well if that is very high , you might get the error you wrote.
-
Look for coalesced percent space or your temporary tablespace.
1 - You should haveto coalesced them;
2 - However your temporary tablespaces are bad configured; reanalize their storage parameter according to the tablespace dimension and the kind of queries you use to work
3 - To use yuor db for the moment shoutdown it and than startup it again. This cause all temporary segments to be released.
Bye
-
Thanks for the ans's
It's a 8.1.5 db and
temporary tablespace is 10 GB with
extent management local uniform size 25M.
pctincrease = 0
I could find contents as temporary in dba_tablespaces.
thanks
