OEM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: OEM

  1. #1
    Join Date
    Feb 2001
    Posts
    75
    Hi,

    OEM for locally managed temporary tablespace assigned as temporary tablespace to all users shows used space at 99.7%.

    There are no entries in dba_free_space, dba_segments, dba_lmt_free_space & dba_lmt_used_space for this TS.

    I think this info is not correct. It is like some HWM. I do not get errors for temp space allocation by quries.

    1. How do I see actually how much of temp space is used by all sessions?

    2. Is there any way to show correct info in OEM?

    Kailash Pareek

  2. #2
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Arrow

    The info OEM is showing you is correct.

    Temporary space is not freed when it is no longer being used. The allocated space is available to be used by the next process that needs it. However, the extents can become so fragmented that a process which needs a lot cannot get what it needs.

    There are at least two ways to clean up your temp tablespace:

    1) Shutdown the instance. Bring it back up. Alter the temp tablespace to coalesce free space.

    2) Alter the temp tablespace offline. Wait a few minutes. Alter it back online. Alter it to coalesce free space.

    Tim

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check v$temp_extent_pool and v$temp_space_header


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Feb 2001
    Posts
    75

    OEM & temporary TS

    Hi,

    Thanks for your replies & info. However, Ratcheer, you can not coalesce a temporary tablespace which is locally managed.

    sambhavan, v$temp_space_header shows free blocks 0. I have 12 GB in this TS. I agree that the space allocated is not freed and all space shows used up.

    However, I wanted to know if there is any way to check, how much is actually used at the point in time by all sessions and how to know that it is time for its extention. One can wait till sessions start giving frequent errors, but that is hardly a good choice.

    Kailash Pareek

  5. #5
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Arrow

    -- Determine current status of temp space usage
    select CURRENT_USERS, TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS,
    ADDED_EXTENTS, EXTENT_HITS, FREED_EXTENTS, FREE_REQUESTS, MAX_SIZE,
    MAX_USED_SIZE, MAX_SORT_SIZE
    from v$sort_segment;

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