Oracle temporary tablespace is full. How do we flush it and create more space?

A "Temporary" Tablespace will likely always appear FULL. This is because in a temporary tablespace segments are not allocated, dropped, and re-allocated as they used to be. The segments are merely reused so we do not have the overhead of constant allocation/deallocation. Most of the time they will actually appear full.

If you are getting failed to extend error. Then they are actually in use and can not be reused. Then we should be concerned with either of two things:

1. Do we need more space on temporary tablespace?
2. Why we are using so much space?

You can use the script to find out detail on temp tablespace allocation:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;