找到正在使用temp file的session

有的时候，我们需要删除某个temp file的时候，会发现该tempfile正在被使用，我们无法drop其文件，我们可以用如下的方式来进行。

sys@ORA10G(9.78.218.143)>ALTER DATABASETEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP01.DBF'DROP INCLUDINGDATAFILES;
ALTER DATABASETEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP02.DBF'DROP INCLUDINGDATAFILES
*
ERROR atline 1:
ORA-25152:TEMPFILE cannotbe droppedat thistime
 
 
Elapsed: 00:00:00.23

此时你的temp tablespace中有2个文件，我们得找到哪个session是使用temp file1，哪个session在使用我想删除的temp file2.

我们来找一下。注意tempfile的segment file number，其实是db_files，加上file id。

sys@ORA10G(9.78.218.143)>select file_idfrom dba_temp_fileswhere file_name='E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP02.DBF';
 
   FILE_ID
----------
         2
 
Elapsed: 00:00:00.06
sys@ORA10G(9.78.218.143)>
sys@ORA10G(9.78.218.143)>
SQL> showparameter db_files
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_files                             integer                         200
sys@ORA10G(9.78.218.143)>

所以，那个temp file的id应该是202了。我们往往看到alertlog中有报错说202号文件无法扩展了，你如果去看dba_data_files中，找不到这个文件，而在dba_temp_files中直接找这一号的文件，也找不到，那是因为要用db_files数加上temp file的id才是。

好了。我们继续来找是哪个session在占用这个temp file。

sys@ORA10G(9.78.218.143)>l
  1  selectc.spid,
  2         b.tablespace,
  3         b.segfile#,
  4         b.segblk#,
  5         round(((b.blocks * d.VALUE) /1024 / 1024),2)size_mb,
  6         a.SID,
  7         a.serial#,
  8         a.username,
  9         a.osuser,
 10         a.program,
 11         a.status
 12    fromv$sessiona, v$sort_usageb, v$processc, v$parameterd
 13   whereb.segfile# = &seg_temp_file_id
 14     andd.name ='db_block_size'
 15     anda.saddr =b.session_addr
 16     anda.paddr =c.addr
 17*  orderby b.tablespace,b.segfile#, b.segblk#, b.blocks
sys@ORA10G(9.78.218.143)> /
Enter valuefor seg_temp_file_id:202
old  13: where b.segfile# = &seg_temp_file_id
new  13: where b.segfile# = 202
 
SPID         TABLESPACE                       SEGFILE#    SEGBLK#    SIZE_MB        SID    SERIAL# USERNAME                       OSUSER                         PROGRAM                                                  STATUS
------------ ------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- --------
7496         TEMP                                   202     63753        172        148         43SYS                           HEJIANMIN\Administrator       sqlplus.exe                                             ACTIVE
 
Elapsed: 00:00:00.06
sys@ORA10G(9.78.218.143)>

Kill完之后，你就可以删除你指定的temp file了。

sys@ORA10G(9.78.218.143)>alter systemkill session'148,43';
 
System altered.
 
Elapsed: 00:00:01.00
sys@ORA10G(9.78.218.143)>
sys@ORA10G(9.78.218.143)>
sys@ORA10G(9.78.218.143)>
sys@ORA10G(9.78.218.143)>ALTER DATABASETEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP02.DBF'DROP INCLUDINGDATAFILES;
 
Database altered.
 
Elapsed: 00:00:00.37
sys@ORA10G(9.78.218.143)>

 

原文地址：http://www.oracleblog.org/working-case/find-the-session-use-temp-file/