I've got a similar problem to Mahoori. My temporary tablespace has four temp files shared over 4 logical volumes. This is because we are set up for parallelisation with a degree of 4 on underlying objects (striped logically across 4 volumes). We've tested this set up and it works the fastest for our bespoke applications.
We have a result_set tablespace that holds result sets from queries. This tablespace resides on the same volumes as the temporary tablespace.
I needed to allocate more space to the result_set tablespace, and attempted to free up some space by reducing the size of the temporary_tablespace...................
alter database tempfile 'S:\ORADATA\HCSP\TMP04.DBF' resize 1000 M;
ORA-03297: file contains used data beyond requested RESIZE value
So, anyone know of a way round this...............
Click Here to Expand Forum to Full Width