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?
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
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