ORA-25152: TEMPFILE cannot be dropped at this time

June 21, 2019

While dropping temp files you might have seen this error. 

SQL > ALTER DATABASE TEMPFILE  '/home/oracle/app/test11/oradata/temp1.dat'  DROP INCLUDING DATAFILES;
 ALTER DATABASE TEMPFILE  '/home/oracle/app/test11/oradata/temp1.dat'  DROP INCLUDING DATAFILES
 *
 ERROR at line 1:
 ORA-25152: TEMPFILE cannot be dropped at this time
Checking Temporary Tablespace Usage in Oracle

This error occur because active sessions might be using the tempfiles and at the same time you might be trying to drop it.

In my case mount point utilization hits above the threshold value. Because of high mount point utilization users were not able to login to the database. After checking alert log we observed this ora error. We tried to resize the datafiles but didn’t reclaim much space. After that we checked the temporary tablespace files which was bigger in size. So we were trying to drop and recreate the temp tablespace but database was not allowing us to drop the tempfiles. Below are the steps to resolve this error.

Cause:

The ORA-25152 error occurs when an attempt is made to drop a TEMPFILE being used by online users.

Steps to Resolve ORA-25152

STEP 1: Connect to the instance and check the instance name:
SQL > select host_name,instance_name from v$instance;
 HOST_NAME                                                        INSTANCE_NAME
 ORAHOWDB                                                           TEST11
STEP 2: Check the tempfile name, initial size and max size:
SQL >  select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,STATUS,maxbytes/1024/1024/1024,BYTES/1024/1024/1024 from dba_temp_files ;
 FILE_NAME                                             FILE_ID TABLESPACE_ AUTOEXTENSIBLE  STATUS  MAXBYTES/1024/1024/1024 BYTES/1024/1024/1024
 
 '/home/oracle/app/test11/oradata/temp1.dat              1 TEMP        YES             ONLINE               31.9999847           31.9990234
 '/home/oracle/app/test11/oradata/temp2.dat              2 TEMP        YES             ONLINE                       20                    20
STEP 3: Check if any active sessions are using the temp files:
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
 a.username,a.osuser, a.status
 FROM v$session a,v$sort_usage b
 WHERE a.saddr = b.session_addr;
STEP 4: Kill the sessions using the temp tablespace:
SQL> alter system kill session 'SID_NUMBER, SERIAL#' immediate;
STEP 5:  Drop the temp tablespace:
Now, we can easily drop the existing temp files as well as temporary tablespace.
SQL> alter tablespace TEMP drop tempfile '/home/oracle/app/test11/oradata//temp01.dbf';
 Tablespace altered.

Must Read: