Hi all, I'm quite confused about how Oracle manages Temporary tablespace. I search the Oracle Concept guide and found two, I think,
excerpts which are contradicting each other. I thought Oracle won't do space deallocation until it's restarted. I don't know
if Oracle drops temporay segments(deallocate??) when the statement completes. Below are the two excerpts from Oracle
Concept Guide.. Please advise, thanks
" Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment.
Sort segments exist for every instance that performs sort operations within a given tablespace.
The sort segment is created by the first statement that uses a temporary tablespace for sorting,
after startup, and is released only at SHUTDOWN. An extent cannot be shared by multiple transactions."
"Oracle allocates temporary segments as needed during a user session,
in the temporary tablespace of the user issuing the statement.
You specify this tablespace with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.
If no temporary tablespace has been defined for the user, the default temporary tablespace is the SYSTEM tablespace.
Oracle DROPS temporary segments when the statement completes.Because allocation and deallocation of temporary segments
occur frequently, it is reasonable to create a special tablespace for temporary segments"