Oracle数据库临时表空间-临时文件删除处理

第一步骤：将临时文件offline

alter database tempfile xx offline;
alter database tempfile xx offline;
alter database tempfile xx offline;
alter database tempfile xx offline;

第二步骤：对临时文件进行删除

alter  tablespace temp drop tempfile '/m/oracle/oradata4/temp05.dbf';
alter  tablespace temp drop tempfile '/m/oracle/oradata5/temp06.dbf';
alter  tablespace temp drop tempfile '/m/oracle/oradata5/temp07.dbf';
alter tablespace temp add tempfile '/m/oracle/oradata4/temp05.dbf' size 1G reuse;
alter tablespace temp add tempfile '/m/oracle/oradata5/temp06.dbf' size 1G reuse;
alter tablespace temp add tempfile '/m/oracle/oradata5/temp07.dbf' size 1G reuse;

删除过程：
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命令杀掉会话进程，即可解决问题。

也可以等待一段时间之后，对临时表空间临时文件进行删除即可 。