Urgent : TEMP tablespace

[farrokhp]

Our TEMP tablespace in production database is %99.8 full. I did tablespace coalesce but it didn't free up anything. Why ?? Do I need to add a datafile?

[pipo]

try (assuming default pctincreaseis of 0) :
alter tablespace temp default storage (pctincrease 1);
then
alter tablespace temp default storage (pctincrease 0);
and
alter tablespace temp coalesce;

it should free your space ...

[farrokhp]

Yes you are right Pipo, It worked. Thansk. But why it is like that ??

[pipo]

well that is a good question ... SMON [should] clean the TEMP tablespace whenever needed, but sometimes it doesn't ...
note that the behaviour of the temporary tablespace has changed in Oracle 8 : Oracle now creates a segment, which keeps on growing until it totally fills the tablespace, and then needed space should be reused, nevertheless I have been working on a lot of HP boxes, and some of these had the problem you have encoutered ... what I suggested is just a workaround

[Halo]

Interesting solution. Another way is to bounce the db.

[tuvham]

Well actually it depends on if the temp tbs is created as temporary or permanent. If temporary it never deallocates temp segments(unless you restart the database), if permanent SMON cleans temp segments. Temporary is to prefer because the database doesn´t have the overhead of deallocating extents all the time. The worst situation i have seen is a 30 GB temp tbs created as permanent and with the next_extent size set to default 30(?) Kb. It took oracle 4 days to deallocate the extents and under this time no temporay segments could be allocated EVEN when we created a new temp tbs!!!

/Tuve

[Halo]

I've had a a temp ts as permanent (not temporary) and I still had to bounce the db to clean out the temp ts. How strange.