【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