My Oracle Support Banner

Dropped Temp File Is Still Visible In Database And Remains On File System (Doc ID 1413933.1)

Last updated on FEBRUARY 24, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 09-Sep-2016***

Symptoms

After dropping a tempfile using standard DDL, the file remains visible in the DBA_TEMP_FILES and V$TEMPFILE views and physically remains on file system, e.g.

SQL> select * from dba_temp_files order by tablespace_name;

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT   MAXBYTES MAXBLOCKS  INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
<PATH>/temp_f04.dbf
         6 TEMP                                                 OFFLINE


SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         6      32937457307 12/30/2011         3          4 OFFLINE READ WRITE
         0          0   8589934592       8192
<PATH>/temp_f04.dbf



NOTE:

1. The session trying to drop the tempfile receives an ORA-25152 error:

SQL> alter database tempfile '<PATH>/temp_f04.dbf' drop;

alter database tempfile
'<PATH>/temp_f04.dbf' drop
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time



2. For a session still using the tempfile at this time a query fails with :

...
ERROR:
ORA-01135: file 201 accessed for
DML/query is offline
ORA-01110: data file 201: '<PATH>/temp_f04.dbf'

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.