Locally Managed TEMP tablespace

[rpardip]

Oracle 817 (64-bit) on Solaris8.

We have created a locally managed temporay tablespace using TEMPFILE option of the size 2GB. It seems to be atlest 95% utilized all the time and SMON not seems to be cleaning up.

As you know on temporary tablespaces, we can not run ALTER TABLESPACE TEMP coalesce.

What could be the reason why SMON is not cleaning up/coalescing periodically? Is there any setting or command that I use to see that the TEMP tablespace is coalesced and freed up?

[marist89]

This is a basic concept that people just don't seem to understand. TEMPORARY tablespace doesn't get de-allocated, it gets reused.

[rpardip]

Thanks Jeff!
I do understand that it gets recycled or reused.
However, I was curious to know if there is a way
to do this with TEMP tablespace.

Thanks again!

[marist89]

If you understand it gets reused, why would you think it needs to be coalesced?

[rpardip]

Perhaps, I thought there would be a way to coalesce TEMP tablespaces
like the option we have with NON-TEMP tablespaces.

Anyway, lets end the discussion here.
Thanks for your clarification!

[pando]

well LOCALLY MANAGED TEMPORARY TABLESPACE uses uniform size extents, if these extents are reused then there is no point coalescing a tablespace, all space is reused over and over again