查询DBA_TEMP_FILES报错，删除临时表空间报错ORA-60100

SYMPTOMS

  查询DBA_TEMP_FILES报错如下图

ORA-01157: cannotidentify/ock data fle 201 -see DBWR trace fle
ORA-01110: data fle 20 1: 'D:APPADMINISTRATORIORADATA MARTIDATAFILE'
01157,00000-"cannotidentify/ock data fle %s -see DBWR trace fle"*Cause: The background process was either unable to find one of the datafles or failed to lock it because the fle was already in use.The database will prohibit access to this fle but other fles will!
be unaffected, However the frst instance to open the database willneed to access all online data fles, Accompanying error fom theoperating system describes why the fle could not be identifed.Action: Have operating system make fle available to database. Then eitheropen the database or dO ALTER SYSTEM CHECK DATAFILES.

查询 v$tempfile 可以看到有一个文件的状态不正常，应该是迁移时文件损坏了

select * from v$tempfile

SOLUTION

优先恢复系统应用为第一位

1.查询当前用户的默认表空间

select username, temporary_tablespace from dba_users;

2.创建新的临时表空间

CREATE temporary TABLESPACE NTEMP TEMPFile 'xxxxxxxxxxxxx' SIZE 100m autoextend on next 100m maxsize 30g;

3.将系统的默认临时表空修改为新建的临时表空间,并检查用户默认临时表空间

alter database default temporary tablespace ntemp;
select username, temporary_tablespace from dba_users;

4.删除有问题的临时表空间

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

删除时报错 如下，有可能是因为在被使用没有释放，也有可能出发Bug 26418088.
BUG:26418088 - FG LEVEL ENQ: TS - CONTENTION WHILE DROPPING TEMPORARY TABLESPACE

首先查询当前系统中哪些sid在使用temp表空间，如果能查到则kill掉 再尝试drop

SELECT s.sid, s.serial#, s.username, s.osuser, t.tablespace, t.segfile#, t.segblk#, t.blocks
FROM v$sort_usage t, v$session s
WHERE t.session_addr = s.saddr;

查询temp使用率

SELECT d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.extent_management like 'LOCAL' 
AND d.contents like 'TEMPORARY';

此案例应该是触发了bug，官方建议是restart db然后再尝试drop temp

重启后可以正常删除

参考文档：

ORA-60100: Dropping Temporary Tablespace With Tablespace ID Number (tsn) n Is Blocked Due To Sort Segments (Doc ID 2696984.1)
BUG:26418088 - FG LEVEL ENQ: TS - CONTENTION WHILE DROPPING TEMPORARY TABLESPACE
NOTE:26418088.8 - Bug 26418088 - 'enq:TS - contention' while dropping temporary tablespace