How to shrink temporary tablespace if it goes huge

Goal:
Your production database has bi-annual or annual MIS reports program which performs huge sorting. Due that your Temp tablespace grows very much and occupies most of the space on file system. Now as the MIS reports process is run probably once or twice a year you don't want to keep a huge tempfile around for the rest of the 364 days and don't want to go out to buy disks.

You have created temp tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA-1652 Text: unable to extend temp segment by %s in tablespace %s

You have tried "alter database datafile .. resize" which always fails with Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or any other purpose.

Fix:
Create new temporary tablespace with smaller size.

SQL>
create temporary tablespace TEMP
tempfile '/iaa/oradata/u01/TABSIAA/temp01.dbf' size 100M
extent management local uniform size 128K;

SQL> alter database default temporary tablespace temp2;

SQL> drop tablespace temp including contents and datafiles;

If any users are using the temporary tablespace currently, you would not be able to drop the tablespace. So, let us find out wihich users are using the temporary tablespace right now using bellow query:

SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)'M' "SIZE",
a.sid','a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

Then kill or wait until the above users finish their jobs, and then try to drop the temporary tablespace again.

You can also see the usage of temporary tablespace using bellow query:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;

Cheers !