之前有做临时表空间的切换,切换后没drop tablespace就删除了temp01.dbf结果排序跟查dba_temp_files报错
SQL> select tablespace_name,file_id,file_name,bytes/1024/1024 Mbytes from dba_temp_files;
select tablespace_name,file_id,file_name,bytes/1024/1024 Mbytes from dba_temp_files
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/home/oracle/oradata/osa/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
两种方法可以恢复
1、重启数据库
重启后系统会自动重建,数据库会提示re-creating在bdump的alter_sid.log中
Re-creating tempfile /home/oracle/oradata/osa/temp01.db
2、重建
alter tablespace temp add tempfile '/oracle/oradata/osa/temp03.dbf' size 50m;
alter tablespace temp drop tempfile '/oracle/oradata/osa/temp01.dbf' ;