Unanswered: TEMP tablespace growing without control
i have migrated an oracle istance from 8.1.6 to 9.2. Everything seems to work normally but i have a problem with the TEMP tablespace. some application are working on data with sort operations and the TEMP tablespace keep on growing without releasing any space. the thing is that the automatically extend of my TEMP tablespace make my file system almost full after some time.
tha same application, working with the older 8.1.6, didn't show this strange behaviour (the old TEMP tablespace was 1 GB and it was more than enough). right now it grows over 2 GB...
the only solution i have is to create a new TEMP tablespace and remove the growing one every two/three days...
is this a problem related to the application or to my database?
how can i solve it?
I have never, in all the systems I have worked with, had temp tablespace with autoextent on. I just set it at a size, and let it like that. There's no problem with TEMP to be apparently 'full' -- it is just the way it is on a healthy system.
I suggest you (if can): uncheck autoextent for TEMP, shut down the db to release the occuped TEMP space, set it at your desired size and then put the db back online.
TEMP never releases space unless it has to; if autoextend is enabled then TEMP will just keep on autoextending. Disable autoextend & you will see TEMP getting fuller & fuller, until it's just about full & then it appears to stop filling up. This is because TEMP uses all available free space, and then as & when more free space is needed TEMP just overwrites old space (and keeps on overwriting old space).
90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.