ORA-25152: TEMPFILE cannot be dropped at this time

While trying to drop the tempfile, I was getting below error.


SQL> create temporary tablespace temp1;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.


SQL>select username,temporary_tablespace from dba_users where temporary_tablespace='TEMP3'
no rows selected




SQL> alter database tempfile '+ICMQA_DATA_01/icmqa/tempfile/temp3.473.774200997' drop including datafiles;
alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

find out which sessions are still using the old temp tablespace.

SELECT a.INST_ID,b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;



Now kill all those sessions
SQL> alter system kill session '7618,4566' IMMEDIATE;

System altered.

or if there are more sessions like in the screenshot create the script to kill all the sessions as below:


SELECT 'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||''' IMMEDIATE;'
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
and b.tablespace='TEMP3' <-- change the tablespace name
and a.inst_id=2 <-- change the instance name
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;




SQL> alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles;

Database altered.

SQL>