有的时候，我们需要删除某个temp file的时候，会发现该tempfile正在被使用，我们无法drop其文件，我们可以用如下的方式来进行。
|
1 2 3 4 5 6 7 8 |
sys@ORA10G(9.78.218.143)> ALTER DATABASE TEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP01.DBF' DROP INCLUDING DATAFILES; ALTER DATABASE TEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP02.DBF' DROP INCLUDING DATAFILES * ERROR at line 1: ORA-25152: TEMPFILE cannot be dropped at this time Elapsed: 00:00:00.23 |
此时你的temp tablespace中有2个文件，我们得找到哪个session是使用temp file1，哪个session在使用我想删除的temp file2.
我们来找一下。注意tempfile的segment file number，其实是db_files，加上file id。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
sys@ORA10G(9.78.218.143)> select file_id from dba_temp_files where 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> show parameter 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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
sys@ORA10G(9.78.218.143)> l 1 select c.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 from v$session a, v$sort_usage b, v$process c, v$parameter d 13 where b.segfile# = &seg_temp_file_id 14 and d.name = 'db_block_size' 15 and a.saddr = b.session_addr 16 and a.paddr = c.addr 17* order by b.tablespace, b.segfile#, b.segblk#, b.blocks sys@ORA10G(9.78.218.143)> / Enter value for 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 43 SYS HEJIANMIN\Administrator sqlplus.exe ACTIVE Elapsed: 00:00:00.06 sys@ORA10G(9.78.218.143)> |
Kill完之后，你就可以删除你指定的temp file了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
sys@ORA10G(9.78.218.143)> alter system kill 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 DATABASE TEMPFILE 'E:\ORA10G\ORACLE\ORADATA\ORA10G\TEMP02.DBF' DROP INCLUDING DATAFILES; Database altered. Elapsed: 00:00:00.37 sys@ORA10G(9.78.218.143)> |