How Oracle Manage TEMP tablespace
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"
I think that the first experts is for TEMPORARY TABLESPACE, and the second experts is for PERMANENT TABLESPACE which is specified as default temporary tablespace to a certain user.
This is another experts: "a single sort allocates its own temporary segment in the temporary tablespace of the user issuing the statement and then returns the extents to the tablespace. Multiple sorts, however, can use the segments in a temporary tablespace designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort but remain available for other multiple sorts."
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Click Here to Expand Forum to Full Width