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