Dropped Temp File Is Still Visible In Database And Remains On File System

(Doc ID 1413933.1)

Last updated on SEPTEMBER 09, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 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
------------ --- ---------- ---------- ------------ ---------- -----------
/u09citcw/oradata/CWCIT0/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
/u09citcw/oradata/CWCIT0/temp_f04.dbf



NOTE:

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

SQL> alter database tempfile '/u09citcw/oradata/CWCIT0/temp_f04.dbf' drop;

alter database tempfile
'/u09citcw/oradata/CWCIT0/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: '/u09citcw/oradata/CWCIT0/temp_f04.dbf'

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms