How to clean up temporary tablespace in Oracle?

To clean up Oracle temporary tablespace, you can follow these steps:

1. First, connect to the Oracle database instance.
2. Check the sessions currently using temporary tablespace.
3. Choose s.sid, s.serial#, s.username, t.tablespace, t.blocks
   FROM v$session s, v$sort_usage t
   WHERE s.saddr equals t.session_addr;
4. This will display the SID, serial number, username, tablespace, and block count of sessions using temporary tablespace.
5. The sessions that are using temporary tablespace can be terminated.
6. Terminate the session with a specified SID and serial number in the system.
7. The ‘sid’ and ‘serial#’ are the SID and serial number of the session retrieved in step 2.
8. Search for and remove temporary segments of temporary table spaces that are no longer in use.
9. Retrieve the owner, name, and type of segments from the database view dba_segments where the tablespace is named ‘TEMP’.
10. This will display the owner, name, and type of temporary segments belonging to the temporary tablespace.
11. Remove the temp file ‘tempfile_path’ from the temporary tablespace.
12. ‘tempfile_path’ is the path of the temporary file to be deleted.
13. Set the size of the temporary tablespace to the desired size.
14. Reduce the size of the temporary tablespace to 100M.
15. This will resize the temporary tablespace to 100MB and ensure it stays that size.
16. Finally, rebuild the temporary tablespace.
17. Add a new temporary file of size 1GB to the TEMP tablespace.
18. The ‘tempfile_path’ is the path of the temporary file to be added, while ‘1G’ is the initial size of the file.

This completes the process of cleaning up the temporary tablespace in Oracle. Please make sure to backup the database before proceeding and ensure that no other processes are affected while cleaning the temporary tablespace.