删除过程：

SQL> alter database tempfile '/u01/app/oracle/oradata/temp2' drop including datafiles; alter database tempfile '/u01/app/oracle/oradata/temp2' drop including datafiles * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time

通过查看官方针对ORA-25152的描述信息，发现如下：

ORA-25152: TEMPFILE cannot be dropped at this time Cause: An attempt was made to drop a TEMPFILE being used by online users Action: The TEMPFILE has been taken offline. Try again, later

可能是临时表空间被占用，执行以下脚本，查询出占用临时表空间的会话信息，使用alter system kill命令杀掉会话进程，即可解决问题，脚本如下：

SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

查看具体会话，构造alter system kill语句

select s."SID", s."SADDR", s."SERIAL#" from v$session s where s."SID" = '15'; 杀掉会话进程的语句需要传递参数，会话ID和"SERIAL#"的值 alter system kill session '15,43';

可以正常的执行删除临时表空间文件的命令

---

或者可以通过如下的脚本，构造批量删除杀掉会话的语句：

select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' immediate;' from v$session a, v$sort_usage b, v$parameter d where a.saddr = b.session_addr and b.tablespace = 'TEMP' order by b.tablespace, b.segfile#, b.segblk#, b.blocks;