Reducing the Temporary Tablespace Size in Oracle 11g

The TEMP tablespace is used when there is a large quantity of sort operations cannot be handled in RAM. It could grow to a very large size if many such database queries are executed at the same time, where a large temporary tablespace is required. Those queries might be badly written or require optimization.

A sustainable long-term solution is to optimize or rewrite the badly written SQL queries.

Meanwhile, if you must free some hardisk space right now, the following is how you should shrink the TEMP tablespace.

Firstly, have a view of the TEMP tablespace usage (numbers are in bytes):

SELECT * FROM DBA_TEMP_FREE_SPACE;

Then, you can decide to shrink the TEMP tablespace online:

ALTER TABLESPACE TEMP SHRINK SPACE KEEP 10G;

You might want to shrink a specific tempfile using the TEMPFILE clause:

ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M;

Note:

You might want to do the shrinking during off peak hours as the TEMP could be busy during the peak hours.