I know it has been said and recommended to use Locally Managed Tablespaces because of their space management and efficiency. However I find hard to believe the way Locally Managed Temporary Tablespace is behaving. NOT DEALLOCATING The free space after session is finished even not at shutdown.
I was on vacation and when I came back I realized that our LMTT has grown from 2G to 18G. The Jr. DBA just keep adding datafiles. and with LMTT there is no option to reclaim the free space.
Has any one experienced the same thing? I am thinking of using DMTT for both TEMPORARY AND RBS tablespace.
Please share your comment
[Edited by Ablakios on 10-18-2002 at 09:43 AM]
10-18-2002, 10:17 AM
SMON does not de-allocate Temporary extents whislt the system is up even when a user process dies. This space will be re-used if a user process needs it.
SMON will de-allocate these extents after the instance has been shutdown and re-started. How long has your instance been up ?
18GB is big and it might take a long time to de-allocate the extents.
Run this query to see if the extents are being de-allocated
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';
if the count is decreasing then it is likely that SMON is cleaning up the temporary extents.
10-18-2002, 10:48 AM
Thanks for the response.The statement shows that extent are being deallocated. However I have about 16gb of wasted space that I can allocate somewhere else that I cannot reclaim. RECLAIMING THE SPACE is my concern.
10-18-2002, 10:51 AM
You could drop the temp tablespace and re-create it.
10-18-2002, 11:54 AM
Well it's your DBA's criteria. Why he adds files? Does he get errors? Or he simply query some v$ views and see there are no freespace? With temporary LMT (and temporary DMT) even it says there is no freespace it does have them.
Your problem is not why my TEMP grows, rather your DBA needs some reading on Oracle Concepts