OEM

[kkpareek]

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

[ratcheer]

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

[sambavan]

Check v$temp_extent_pool and v$temp_space_header


Sam

[kkpareek]

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

[ratcheer]

-- 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;