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
do :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
this will free unused space
why can't I just do 'alter tablespace TEMP coalesce' ?..do I have to do the first two statements that you suggest 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 :)
is this applies to other tablespaces as well other than TEMP tablespaces?
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 ;)
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?...
is your tablespace locally managed ???
select extent_management
from dba_tablespaces
where tablespace_name = 'TEMP'
Yes, my TEMP tablespace is locally managed.....
you have to increase datafile size, you cannot coalesce LMT tablespace (there is no point anyway)
if my other tablespaces are all locally managed, I also have to increased the datafile sizes, there is no other way to free up my tablespaces?
well if they are not full you dont have to increase the datafile size, LMT reuses the spaces effiently (not saying 100%) because with LMT you get 0% fragmentation
if you are using a join in your sql and the column in the where clause is not having an indx on it and/ or its position is in a composite index is not allowing the usage of index the sort will be done in the temp tablespace. so depending on the cardinality of the column adding a index to this column will greatly improve the queries performance. if that does not help
try
- coalescing the temp tablespace.
- increasing the sort_area_size parameter.
- increasing the temp tablespace.