I have 1000M TEMP TABLESPACE which is 99% full for last couple weeks...my question is can I leave it like this or do I need to clean this up?...how do I free temp tablespace?...
Printable View
I have 1000M TEMP TABLESPACE which is 99% full for last couple weeks...my question is can I leave it like this or do I need to clean this up?...how do I free temp tablespace?...
Leave it. The fact that you reached 99% may indicate you need more TEMP.
1) if you run Oracle 8, then the behaviour is normal, the tablespace is filled, and then Oracle manages to free temp segments when space is needed ... but sometimes it tells you that TEMP tablespace is full, well I never understood this kind of bug :/
well anyway, if you do not have problems saying 'unable to extent ... segment in TEMP tablespace', then it works fine.
2) if you still want to free space :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
this will free all unused segments in your TEMP tablespace
If you are sure that 1000M TEMP ts is enough for you, it will be good to have it cleaned up sometime - the next scheduled downtime on your system.
If you are seeing space related errors in your alert log, you either need to add a datafile or clean-up TEMP segments.
Some processes that get terminated abnormally (may be because killed), leave these segments in TEMP ts.