The database version is 126.96.36.199. There is only one application schema running with a size < 1G. We have allocated 2G initially for temp space. The temp tablespace was full and increased to 5G, 8G and now 13G. The temp space is not
deallocating after the sorts. We are keep on increasing the space when there is an error ORA-01652: unable to extend temp segment by 128 in tablespace.
I planned to create a new temp tablespace and assign that as default temp tablespace and can drop the old temp space for reclaiming space. But, the problem may repeat again with the new temp space. I have the same problem on one of the 10.2.0.3 database also. All these databases will run for 24/7. Is there any solution for this instead of restarting the database?.
Try to find out which sessions are using TEMP tablespace. Try to nail down query which is causing the huge TEMP tablespace usage.
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
It can be possible that several different sessions are creating TEMP segments for sorting activities, so you are not able to see it deallocting.
I agree with Dilip. You need to track down the session/SQL using sort cause your problem description indicates some process is using large amounts of sort area. If your system supports adhoc queries you could have a user selecting all the data in your largest table in a specified order or you may have SQL being solved via a plan that uses too much temp space. A single SQL statement might need sort space to support a hash join, a group by, and an order by all at once.
Poor statistics could be responsible but the only way to know is to search out the problem session(s) and see what they are doing.
Yes. There are no long running and complex joins in the queries. But, almost 47G of temp space is used and not deallocated. It is just keep on increasing. The used blocks are not released. Is there any solution for this to avoid.
Dilip, yes there are known issues with information in v$temp_space_heade not being correct.
I agree the OP needs to capture the information in v$sort_usage on a regular basis for sessions using large amounts of temp as previously stated in the thread.
I would not be using expandable data files for temp. I would give it a set number of fixed data files. If the space is exhaused Oracle will return errors and I would try to grap the v$sort_usage information.
This might be a case where a database event trigger on server error might be of use though I think i would just try capturing the sort usage first.
1. Why your application need so much of temporary tablespace? With few G of data running 47G temp tablespace that usually spell wrong queries. I have seen that happening in our dev environment, it's a result of a cartesian join. You need to use your v$sort_usage to capture the query that using most temp segment at time of peak usage. Or run statspack report during busy hour to start with.
2. Temp tablespace is not released. That's not a problem by itself. To more efficient manage the temporary tablespace usage. The temporary (sort) segment will not be de-allocated after being used first time. It will be re-used. That save Oracle a lot of time to keep allocate/deallocate temp segments. It's expected behavior. Check this metalink doc for more info.
So in your case, that's bit of problem because you know you don't need such big size of temporary tablespace. That's a result of a runaway query. If you need to claim these space back, you need to create a new temporary tablespace switch default temporary tablespace to new one and drop old one. Or if you can restart instance, you can shutdown and start in restrict mode to resize your temporary tablespace datafile. Oracle will deallocate temp segment at next startup.
Of course you need to deal with the culprit query first, otherwise it will keep increase your temp tablespace. Or just cap your temp tablespace at 10G and let it fail.
I don't understand the point..."+Once OS file space is allocated to a datafile that space is not released by Oracle for the life of the datafile. True tempfiles only live for the life of the database instance+.". This may be correct. But, it has to display the temp space as free blocks once sorting is complete, eventhough it's not deallocted the OS space.
My point is in your inital post it is not clear that you do not expect unused temporary file space to be release at the OS level. I was making sure that thread readers see that point that once an Oracle file takes space at the OS level it keeps it barring DBA action.
My first point noted that once allocated to a sort an extent is never released. It is available, free, for reuse, but it is allocated to the sort segment. The sort segment(s) are visible in v$sort_segment and the contents of the sort segment are visible in v$sort_usage.
But your problem is you need to find those sessions consuming the temporary tablespace. Set up a query against v$sort_usage to capture the users where blocks or extents are over some threshold value. Eventually your problem sessions should appear. Any temporay lob usage in your application? Huge hash joins?
There are no changes in my application code since moved to production. We have initially allocated 5G. This space was filled-up after 2 or 3 months. I have added additional space and this is also filled up and raised ORA-01652 error. I can't say how frequent the space gets full. That may be depends on the how much additional space I have added.
If there are no changes in the SQLs, it has to use same temp space every time, ofcourse that depend on the number of sessions. If I monitor for a month, it has to work in a similar way in the second month also.