【Oracle】恢复丢失的临时表空间文件

Oracle 11g以后，临时表空间文件是可以在重启数据库以后自动生成的（当然也可以在相同目录再建一个临时表空间文件），模拟实验如下：
1）删除临时表空间数据文件

SYS@ENMOEDU> select file_name from dba_temp_files;

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

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

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

 

2）创建大表，并进行排序
由于PGA空间有限，所以使用临时表空间进行排序，由于缺少临时表空间文件，故会报错：

SYS@ENMOEDU> create table bigtab as select * from dba_objects;

Table created.

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

87213 rows created.

SYS@ENMOEDU> /
174426 rows created.

SYS@ENMOEDU> /

348852 rows created.

SYS@ENMOEDU> commit;

Commit complete.

SYS@ENMOEDU> select * from bigtab order by 1,2,3,4,5,6,7,8,9,10;
select * from bigtab order by 1,2,3,4,5,6,7,8,9,10
              *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/ENMOEDU/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

3）重启数据库，数据库会自动重建临时数据文件

SYS@ENMOEDU> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             507513936 bytes
Database Buffers          318767104 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SYS@ENMOEDU> !ls/u01/app/oracle/oradata/ENMOEDU/temp01.dbf
/u01/app/oracle/oradata/ENMOEDU/temp01.dbf