DBA_FREE_SPACE and TEMP datafiles

[blurry]

Ok here is the situation: Developer calls me stating that she is running a second attempt at running a Discoverer Report that will run 4 hours.

The previous attempt to run the report failed 4 hours into it stating that it couldn't extend in the TEMP tablespace.

The second time she ran the report I monitored DBA_FREE_SPACE, watching the space go down. When it got to 40Mb left I added 100Mb datafile to TEMP tablespace. I continued to watch it go down to .038Mb and the report finished. I was amazed, but suddenly started wondering if that could be accurate. How lucky would that have been to add exactly what I needed to finish the job!

Ok, now the report ran without the error, and the free_space queries I do say that the TS has .038 free. The space has not come back to the view. Is that because it will be reused or what?

Could anyone shed some light on if you can really tell how full your TEMP tablespace is by using the DBA_FREE_SPACE view?

Thanks !

[blurry]

I just refreshed my memory. Temp segments extend until they are no longer needed by the process then they are dropped. Shouldn't that drop happen as soon as the developer's script stops and he or she logs off?

[sln81]

In the temp tablespace, the temporary segment keeps growing till the database is shutdown. No deallocation is done till that time. Hence you might be seeing the result like that.

[blurry]

Thanks. So after you fill up your TEMP datafiles from the time you last bounced your server IS there a way to find out how much real temp space you have left?

[sln81]

You can check it from the view V$SORT_USAGE to see how much space is being used by current sort operations.