How I can free the temporary tablespace

[mkumarnk]

HI,

Is there any way to 'free' the temporary tablespace allocated segments other than shut down instance.Will the coalascing command will work for temporary tablespace.

Any advise ...

Manoj

[nvamsi]

Hi,

I don't think you can really deallocate or coalesce space fom the temporary tablespace of type 'TEMPORARY'.

The temporary (sort) segment of a given temporary tablespace is created at the time of the first sort operation, which has to write to disk to free up sort space in memory. The first disk sort creates a sort segment in the temporary tablespace. Multiple transactions which need asort on disk, can share the same sort segment, however, they cannot share the same extent. The sort segment expands by allocating new extents. The sort extents are not de-allocated while the instance is running, but are marked as free and can be re-used as required.

Thanks

[mkumarnk]

Hi,

Thanks for the reply..

How can I find whether it is marked as free ?

Manoj

[tamilselvan]

Select from sys.fet$ to see the freed blocks.

[pando]

v$sort_segment

[skdas]

The following Queries may help you, how much TEM tablespace is used and how much free.

--SORT AREA SIZE

SELECT tablespace_name, extent_size, total_extents, used_extents,
free_extents, max_used_size
FROM v$sort_segment;

SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr;

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
from v$sort_segment;

[mkumarnk]

Hi,

Thanks a lot for the inputs...

Let me work out..

Manoj