Shrink Temporary Tablespace
Steps:
1. Create a temporary tablespace for swapping
create temporary tablespace temp_temp tempfile
'/dbora01/oradata/ORCL/ora02/temp_temp01.dbf' size 100M
extent management local uniform size 1m;
2. Use it as default temporary tablespace
alter database default temporary tablespace temp_temp;
3. Drop the old temporary tablespace
alter tablespace temp tempfile offline;
drop tablespace temp including contents;
4. Create back the original temporary tablespace and put online
create temporary tablespace temp tempfile
'/dbora01/oradata/ORCL/ora02/temp01.dbf' size 5000M reuse,
'/dbora01/oradata/ORCL/ora03/temp02.dbf' size 5000M reuse
extent management local uniform size 1m ;
alter tablespace temp tempfile online;
5. Set the default temporary back to the original one
alter database default temporary tablespace temp;
6. Drop the temporary one
drop tablespace temp_temp including contents and datafiles;
7. End