Drop TEMP File in the Oracle Database

drop tempfile in Oracle database

Unlike Oracle datafiles which make up a tablespace, it is possible to drop an individual TEMP file from a TEMPORARY type tablespace.

You can use statement: ALTER DATABASE TEMPFILE ‘<tempfile_location>’ drop including datafiles;

When doing this, sometimes the statement will execute but the tempfile won’t drop from the file system. You try to delete it but to no avail. You would like to drop the TEMP file from the file system because otherwise it will probably get forgotten about.

I’ve found that by adding another TEMP file with the reuse command to overwrite the one you just dropped, and then running the above statement again you can usually drop the tempfile with the original statement. Just be careful not to write over the wrong TEMP file.

You can also read about dropping and recreating TEMP files in my other article about how to move Oracle datafiles.

Like it, share it...

Category: Database Config

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *