问题:
Oracle经过Rman恢复后未重建临时表空间导致查询dba_temp_files报错:
SQL>select tablespace_name,file_name from dba_temp_files;
select tablespace_name,file_name from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/oradata/datafiles/nmsa/temp01.dbf'
Oracle经过Rman恢复后未重建临时表空间导致查询dba_temp_files报错:
SQL>select tablespace_name,file_name from dba_temp_files;
select tablespace_name,file_name from dba_temp_files
*
ERROR at line 1:
ORA-01187: cannot read from file 201 because it failed verification tests
ORA-01110: data file 201: '/oradata/datafiles/nmsa/temp01.dbf'
解决方法:
查看v$tempfile确定有几个temp临时表空间和对应的临时数据文件:
SQL>select file#,ts#,name,status from v$tempfile;
FILE# TS# NAME STATUS
---------------------- ------------------------ ------------------------------------------------- ------------------------
1 3 /oradata/datafiles/nmsa/temp01.dbf ONLINE
临时表空间reuse:
SQL>alter database tempfile '/oradata/datafiles/nmsa/temp01.dbf' drop;
Database altered.
SQL>alter tablespace temp add tempfile '/oradata/datafiles/nmsa/temp01.dbf' reuse;
Tablespace altered.
SQL>select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
--------------------------------- ----------------------------------------------------------------
TEMP /oradata/datafiles/nmsa/temp01.dbf