drop tempfile后，这些tempfile在数据库的视图中依然可见。dba_temp_files or v$tempfile

某套数据库的alert日志中频繁地报数据坏块，而这些坏块都属于某个temporary表空间。由于计算drop存在坏块的tempfile，然后新增tempfile。

 

在执行alter tablespace TS_TEMP drop tempfile '......';时，执行成功。但检查dba_temp_files时，该tempfile在视图中仍然存在，检查了alert日志，也提示成功drop了该tempfile。

 

搜索到《Dropped Temp File Is Still Visible In Database And Remains On File System (Doc ID 1413933.1)》这篇文章，才知道原因。

 

The situation is caused by sessions that were using the information in the temporary file that was being dropped.

The dropping of a temporary file is done in 2 steps:

1. The temp file is taken offline so no new sessions can access it
   
   
2. If there are no sessions accessing the sort information in the temp file then it is dropped. If not then it is not dropped until these sessions are done with using the temp file or the sessions are exited/killed.