Similar to Reduce Size of UNDO Tablespace, you need to recreate temporary tablespace in order to reduce the size. Issue a resize statement might result error below:
alter database tempfile '/u01/database/temp1.dbf' resize 250M
ORA-03297: file contains used data beyond requested RESIZE value
To recreate the temporary tablespace, you need to create a second temporary tablespace:
- Create second temporary tablespace
create temporary tablespace temp2 TEMPFILE '/u01/database/temp2.dbf'size 5M reuse autoextend on next 1M maxsize unlimited extent management local uniform size 1M;
- Set second temporary tablespace as default
alter database default temporary tablespace temp2;
- Drop the first temporary tablepsace
drop tablespace temp including contents and datafiles;
- Recreate first temporary tablespace
create temporary tablespace temp tempfile '/u01/database/TEMP01.dbf' size 300M reuse autoextend on next 50M maxsize unlimited extent management local uniform size 1M;
- Set first temporary tablespace as default
alter database default temporary tablespace temp;
- Drop the second tablespace
drop tablespace temp2 including contents and datafiles;