ORA-1652: unable to extend temp segment by 1280 in tablespace TEMP

[gaegoori]

Hi, I'm getting unable to extent tablespace error. My temp size is 1200M and it's full. What should I do before increase the Temp tablespace size?...can I coalese temp tablespace?...is increasing the Temp tablespace right way to solve this problem?
Thanks

[pipo]

do :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;

this will free unused space

[gaegoori]

why can't I just do 'alter tablespace TEMP coalesce' ?..do I have to do the first two statements that you suggest pipo?

[pipo]

because temporary segments are not freed, but just marked as unused when they are not used (if you coalesce, you won't change much things), so by changing pctincrease, you force SMON to scan the whole tablespace, and unused segments are then freed, then you change back to pctincrease=0, and you can coalesce since you now have plenty of free segments everywhere

[gaegoori]

is this applies to other tablespaces as well other than TEMP tablespaces?

[pipo]

nope, free segments in other tablespaces really are freed. this behaviour is due to the fact that Oracle manages temp segments, and uses unused segments when needed, while in other tablespaces segment management is quite basic : if you drop an object, all of its extents are freed, if not then extents are used
so you can directly coalesce a standard tablespace, no need to use this SMON workaround

[gaegoori]

Thank you pipo....
I tried the first statemnet and I got the following error...

SQL> alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 1)
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

is this syntax right?....now what it I do wrong here?...

[pipo]

is your tablespace locally managed ???

select extent_management
from dba_tablespaces
where tablespace_name = 'TEMP'

[gaegoori]

Yes, my TEMP tablespace is locally managed.....

[pando]

you have to increase datafile size, you cannot coalesce LMT tablespace (there is no point anyway)