Re: resize tablespace temp

As you may have found out, the TEMP tablespace is a little trickier than others tablespaces.
If you have multiple files making up your TEMP tablespace in Oracle 9, you can drop and recreate one file at a time, avoiding having to drop and recreate the whole tablespace, and possibly altering user's default tablespace. (if you don't, you should consider it)

1.ALTER DATABASE TEMPFILE '...temp02.dbf' OFFLINE;
2.ALTER DATABASE TEMPFILE '...temp02.dbf' DROP INCLUDING DATAFILES;
3.alter tablespace temp add tempfile '...temp02.dbf' size 1000M reuse
           autoextend on next 500M maxsize 8000M;