PROBLEM:

While dropping a tempfile got the below error.

SQL> alter tablespace TEMP drop tempfile ‘/archive/NONPLUG/NONCDB/temp01.dbf’;
alter tablespace TEMP drop tempfile ‘/archive/NONPLUG/NONCDB/temp01.dbf’
*
ERROR at line 1:

ORA-25152: TEMPFILE cannot be dropped at this time

Solution:

If we try to drop a tempfile, while active sessions are still using that particular tempfile, then this error hits.  So the solution is to kill those sessions.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------
/archive/NONPLUG/NONCDB/temp02.dbf
/archive/NONPLUG/NONCDB/temp01.dbf

Check the session using temp:

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; 

output

TABLE   SEGFILE#    SEGBLK#     BLOCKS        SID    SERIAL# USERNAME OSUSER   STATUS
----- ---------- ---------- ---------- ---------- ---------- -------- -------- --------
TEMP         201      58624        128        331      20052 DBSNMP   oracle   ACTIVE
TEMP         201      58240        128        337      25558 DBSNMP   oracle   ACTIVE

Kill the above sessions:

alter system kill session '331,20052' immediate;
alter system kill session '337,25558' immediate;

Now drop the temp again:

SQL> alter tablespace TEMP drop tempfile '/archive/NONPLUG/NONCDB/temp01.dbf';

Tablespace altered.