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