Shrink Temporary Tablespace

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