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
- coalescing the temp tablespace.
- increasing the sort_area_size parameter.
- increasing the temp tablespace.