Locally Managed Temporary Tablespace.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Locally Managed Temporary Tablespace.

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi All,
    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]
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Sep 2001
    Posts
    62
    Hi,

    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.

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi Sazzadur
    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.
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Sep 2001
    Posts
    62
    You could drop the temp tablespace and re-create it.


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width