1.删除临时表空间数据文件
SYS@PROD1>select file_name from dba_temp_files;

FILE_NAME
------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/temp01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/temp01.dbf
ls: /u01/app/oracle/oradata/PROD1/temp01.dbf: No such file or directory

2.创建张大表，并对大表进行排序

由于PGA空间有限，所以会使用临时表空间，由于临时表空间的数据文件已经丢失，故会报错
SYS@PROD1>create table bigtab as select * from dba_objects;

Table created.

SYS@PROD1>insert into bigtab select * from bigtab;

86972 rows created.

SYS@PROD1>/

173944 rows created.

SYS@PROD1>/

347888 rows created.

SYS@PROD1>commit;

Commit complete.

SYS@PROD1>select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;
select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
*
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD1/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3.重启数据库，数据库会自动重建临时数据文件
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup;
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 528485456 bytes
Database Buffers 301989888 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/temp01.dbf
/u01/app/oracle/oradata/PROD1/temp01.dbf

注此功能为11g新特性，当然，读者也可以自行手工创建临时表空间数据文件，解决此问题。

ITPub博客

【备份恢复】自动tempfile恢复

最新文章