- 05 Nov, 2021
- 0 Comments
- 1 Min Read
ORA-25152: TEMPFILE cannot be dropped at this time
ORA-25152: TEMPFILE CANNOT BE DROPPED AT THIS TIME
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
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 ORALEARNOMATE 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.