ORA-25152: TEMPFILE cannot be dropped at this time

The temporary tablespace can have more than one tempfile. You can drop the tempfile by running the following command:

Advice: You should do this when no user is using the database.

Oracle 9i R2 and above:

alter database tempfile 'd:\oracle\oradata\sid\temp04.dbf' drop including datafiles;

Oracle 10g R2 and above:

alter tablespace temp drop tempfile 'd:\oracle\oradata\sid\temp04.dbf';

The commands above are usually successful if there is no active session using the tempfile. The tempfile do not need to be in OFFLINE status in order for it to be dropped. However, you can bring it OFFLINE first before dropping with the following command:

alter database tempfile 'd:\oracle\oradata\sid\temp04.dbf' offline;

A tempfile cannot be dropped is usually caused by active sessions.

If you receive error such as below, try to kill the active session:

ORA-25152: TEMPFILE cannot be dropped at this time

Find out the active session:

SQL> SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
  2  FROM v$session s, v$sort_usage u
  3  WHERE s.saddr=u.session_addr
  4  ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

       SID USERNAME   STATUS   TABLESPACE  SEGFILE#  CONTENTS  EXTENTS BLOCKS
---------- ---------- -------  ----------- --------- --------  ------- ------
       213 DBUSER1    INACTIVE TEMP        203       TEMPORARY 5279    675712


SQL> select sid,serial# from v$session where sid = 213;

       SID    SERIAL#
---------- ----------

       213      48649

Kill the session:

SQL> alter system kill session '213,48649';

System altered.

Retry drop the tempfile:

SQL> alter database tempfile 'd:\oracle\oradata\oradb\temp04.dbf' drop including datafiles;


Database altered.

If the command above is successful, but the physical tempfile is not deleted. You might have to restart the database and delete the physical file at OS level.