Oracle temp file TEMP01.DBF taking up too much space

Question

Can I delete this file or recreate it with a size limit?

Thanks.

Answer 1

You did not say which version of Oracle you are using. The most common way of doing this is to drop the temporary tablespace and re-create it. If it is the default temporary tablespace, you need to do this when no users are logged in, like during a maintenance window.

Older versions will require you to create a new temporary tablespace, make it the default, and then drop the one you currently have. From 9i on you do not need to do this. I will assume 9i onward - do the following:

$ sqlplus / as sysdba alter database tempfile '/<path>/temp01.dbf' drop including datafiles; alter database temp add tempfile '/<path>/temp01.dbf' size 500m autoextend on next 250m maxsize 2048m; 

Of course you should put in size values that are appropriate for your installation.