Oracle 11g以后,临时表空间文件是可以在重启数据库以后自动生成的(当然也可以在相同目录再建一个临时表空间文件),模拟实验如下:
1)删除临时表空间数据文件
SYS@ENMOEDU> select file_name from dba_temp_files;FILE_NAME
------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/temp01.dbfSYS@ENMOEDU> !rm /u01/app/oracle/oradata/ENMOEDU/temp01.dbfSYS@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