This article will give examples of how to drop and recreate a tempfile in oracle that is associated with a temporary tablespace.

Find Existing Tempfile

We have two views to get the tempfile information: (V$TEMPFILE & DBA_TEMP_FILES). Both views have common information as well as different information like creation_date, tablespace_name, etc. They are also similar to (DBA_DATA_FILES & V$DATAFILE), but they are targeting the tempfile only.

Note: The tempfiles are not showing in normal data dictionary datafiles views like (DBA_DATA_FILES & V$DATAFILE).

Example 1

SELECT TABLESPACE_NAME
      ,FILE_NAME
      ,STATUS
      ,AUTOEXTENSIBLE
FROM   DBA_TEMP_FILES;

Output

Example 2

SELECT FILE#
      ,NAME
      ,STATUS
      ,ENABLED
FROM   V$TEMPFILE;

Output

Note: Sometimes, you will face the error “dba_data_files table doesn’t exist” while selecting from those views, and this is due to the privilege required, So you have to log in with SYSDBA privilege to get the data.

Check Session Using Temp Tablespace

Before dropping the temp file, we have to identify the database sessions using the temp tablespace with our target temp file.

SELECT DISTINCT SU.TABLESPACE
               ,SU.BLOCKS
               ,S.SID
               ,S.SERIAL#
               ,S.USERNAME
               ,S.OSUSER
               ,S.STATUS
               ,'ALTER SYSTEM KILL SESSION  ' || SID || ',' || SERIAL# || ' IMMEDIATE;' KILL_SESSION_COMMAND
FROM   V$SESSION S, V$SORT_USAGE SU
WHERE  S.SADDR = SU.SESSION_ADDR
AND    SU.TABLESPACE = '<temp tablespace name>'
AND    S.STATUS = 'ACTIVE';

The above query gets all active sessions that are using our temp tablespace along with the command to drop each one in this column “KILL_SESSION_COMMAND”. You need to pass your temp tablespace name instead of ‘<temp tablespace name>‘

Output

Don’t forget to check our detailed tutorial about checking tablespace usage

Drop Tempfile Without Datafile

We can drop the existent tempfile of a temp tablespace but without dropping the datafile using the below command:

ALTER DATABASE TEMPFILE 'c:\oracle\vis\db\apps_st\data\tempx03.dbf' DROP
     INCLUDING DATAFILES;

OR

ALTER TABLESPACE TEMP DROP TEMPFILE 'c:\oracle\vis\db\apps_st\data\tempx03.dbf';

The above commands are the same; we have to use one of them. We altered the database to drop this tempfile, but without dropping the datafile. And yes, there is a difference between both. They are not the same thing. We will talk about the differences between both in a separate article.

Check This: Article about Oracle Temp Tablespace Usage if you want to monitor the usage of the temp tablespace in Oracle database.

Drop Tempfile and Datafile Rules

• The database should be open.
• We can’t drop the used temp file.
• If this temp file is the only first file in the tablespace, instead, drop the tablespace itself.
• Oracle NEVER removes data files nor temp files physically

Recreate Tempfile in a Temp Tablespace

Now, after we dropped the tempfile, we can recreate it again by using the following command:

ALTER TABLESPACE TEMP
   ADD TEMPFILE 'c:\oracle\vis\db\apps_st\data\tempx03.dbf' SIZE 20M REUSE;

With the above, we recreate the tempfile by altering the temp tablespace.

Note: We have used the clause “REUSE” because after droped the tempfile above we agrred that Oracle not deleting the physical datafile from the operating system, therefore, if we didn’t spicified the “REUSE” command an error “file already exists” will raised.

Final Thoughts

Dealing with the Tempfiles, which is a part of temp tablespace, is simple because Oracle facilitates this with a very handful of commands to control the tempfiles. This article covers how to drop and recreate the tempfile and the rules for deleting those tempfiles.

If you have a inquiry or doubt don’t hesitate to leave them in comment. we are waiting your feedback as well.

Hopefully, it was clear and concise.
Don’t forget to spread the knowledge by sharing this article