Resizing an Over-Grown Temporary Tablespace

One fine day, while carrying out our daily health checks we came across an over-grown Temporary Tablespace. The Temporary Tablespace had over-grown to 12GB. Normally, our temporary tablespace has not exceeded beyond 1 GB. But due to some one-time un-tuned script that ran the other evening, the tablespace having autoextend set to unlimited had grown to 12GB. We needed to reduce the size of the temporary tablespace to free space on the server, as our server had resource constraints.

You can not reduce the size of the temporary tablespace, despite the % of free space shown. You have to drop and re-create the temporary tablespace. Let me show you how to regain that occupied space by the over-grown temporary tablespace.

Assuming the Default Temporary Tablespace is "TEMP", you create a new temporary tablespace "TEMP02".

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP02" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP02.dbf' SIZE 100M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you set "TEMP02" as your default temporary tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP02";
Database altered.

Then, you drop the over-grown temporary tablespace "TEMP" and recreate the "TEMP" temporary tablespace.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP01.dbf' SIZE 500M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 600M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you make "TEMP" as your default temporary tablespace, and drop the temporary tablespace "TEMP02".

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
Database altered.

SQL> DROP TABLESPACE temp02 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Please ensure that you are carrying out this activity in your Maintenance Windows, or else you may encounter following errors while dropping the temporary tablespace in use:

Errors in file c:\oracle\admin\orcl\udump\orcl_ora_2300.trc:
ORA-01258: unable to delete temporary file C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
ORA-27056: skgfrdel: could not delete file
OSD-04024: Unable to delete file.
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.