ORA-25152: TEMPFILE cannot be dropped at this time问题解决

删除过程：

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 timeCause: An attempt was made to drop a TEMPFILE being used by online usersAction: 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;