Thanks for the question, Russell.
Asked: January 11, 2016 - 5:37 pm UTC
Last updated: January 12, 2016 - 12:53 am UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
I have a 12C database which has a single temporary tablespace that contains 3 temp files. At one point we had our temp files configured so that they would NOT AUTOEXTEND. During executions of our nightly tests we have seen the temp space allocation go to just one of the temp files in the temporary tablespace, some operations need more than the hard limit we had allocated which of course generated ORA-1652 errors.
So my question is what is the allocation strategy for temp files? I would have expected the session to start using the other temp files in the tablespace once the first file was filled up? We recently changed our test platforms to 12C from 11G R2, and I don't remember having this problem before, but can't be certain.
Thanks.
and Connor said...
The number of files should not be of significance, they merely contribute to the total available space within the tablespace.
If you were getting unable to extend errors, then (unless there is a bug I'm unaware of) you had indeed exhausted all the space in *all* of the files.
This is tough to catch - because when the error occurs, your statement crashes and you free up the space. Some things to look at:
a) Keep an eye v$sort_usage, V$SQL_WORKAREA_active whilst your statement/job is running - you can watch the space consumption in real time.
b) Consider setting a resumable timeout within your session, so that if you run out of space - you simply pause rather than crash.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment