环境：

OS：Oracle Linux Server release 5.6

DB：oracle11.2.0.1

1.查看当前数据库使用默认临时表空间；

itkaifang@SYS> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME                  PROPERTY_V DESCRIPTION
------------------------------ ---------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP       Name of default temporary tablespace
itkaifang@SYS> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/itkaifang/temp01.dbf
itkaifang@SYS>

2.创建大于60万行数据的新表用于验证（原理是当PGA大小不能为表排序时，会使用临时表空间）；

itkaifang@SYS> create table txt as select * from dba_objects;
Table created.
itkaifang@SYS> insert into txt select * from txt;
289936 rows created.
itkaifang@SYS> /
579872 rows created.
itkaifang@SYS> /
1159744 rows created.
itkaifang@SYS> select count(*) from txt order by 1;
  COUNT(*)
----------
   2319488
itkaifang@SYS>

3.删除临时表空间数据文件模拟错误；

itkaifang@SYS> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/itkaifang/temp01.dbf
itkaifang@SYS> !rm /u01/app/oracle/oradata/itkaifang/temp01.dbf

itkaifang@SYS> !ls /u01/app/oracle/oradata/itkaifang/temp01.dbf
ls: /u01/app/oracle/oradata/itkaifang/temp01.dbf: No such file or directory
itkaifang@SYS> select * from txt order by 1,2,3;
select * from txt order by 1,2,3
              *
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/itkaifang/temp01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

itkaifang@SYS>it

4.1恢复方法一重启数据库即可；

itkaifang@SYS> shutdown abort
ORACLE instance shut down.
itkaifang@SYS> startup
ORACLE instance started.
Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          306184192 bytes
Redo Buffers                2433024 bytes
Database mounted.
Database opened.
itkaifang@SYS>
itkaifang@SYS> select * from txt order by 1,2,3;
VALID   N N N          1
PUBLIC
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP
------------------------------ ---------- -------------- ------------------- --------- --------- -------------------
STATUS  T G S  NAMESPACE EDITION_NAME
------- - - - ---------- ------------------------------
/2db02c0e_OraCustomizationErro
                                    47749                SYNONYM             15-AUG-09 15-AUG-09 2009-08-15:00:27:17
VALID   N N N          1
PUBLIC
/2db36ed3_ScaleGeneralOpImage
                                    60362                SYNONYM             15-AUG-09 15-AUG-09 2009-08-15:00:32:28
VALID   N N N          1
^CERROR:
ORA-01013: user requested cancel of current operation         ------>此处数据太多直接Ctrl+C结束任务了

10590 rows selected.

itkaifang@SYS>

4.2恢复方法二在不能关闭数据库的情况下使用；

itkaifang@SYS>  create table t1 as select * from dba_objects;
Table created.
itkaifang@SYS> insert into t1 select * from t1;
72483 rows created.
itkaifang@SYS> /
144966 rows created.
itkaifang@SYS> /
289932 rows created.
itkaifang@SYS> /
579864 rows created.
itkaifang@SYS> select * from t1 order by 1;
select * from t1 order by 1
              *
ERROR at line 1:
ORA-01116: error in opening database file 202
ORA-01110: data file 202: '/u01/app/oracle/oradata/itkaifang/temp02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
itkaifang@SYS> select file_name from dba_temp_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/itkaifang/temp02.dbf
itkaifang@SYS> create temporary tablespace temp tempfile'/u01/app/oracle/oradata/itkaifang/temp01.dbf' size 50M autoextend on maxsize 2G;
Tablespace created.
itkaifang@SYS> alter database default temporary tablespace temp;
Database altered.
itkaifang@SYS> drop tablespace temp1;
Tablespace dropped.
itkaifang@SYS> select * from t1 order by 1;
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED    LAST_DDL_T TIMESTAMP
------------------------------ ---------- -------------- ------------------- ---------- ---------- -------------------
STATUS  T G S  NAMESPACE EDITION_NAME
------- - - - ---------- ------------------------------
VALID   N N N          4
APEX_030200
WWV_FLOW_WORKSHEET_DOCS
                                    71157          71157 TABLE               2009-08-15 2009-08-15 2009-08-15:00:43:24
VALID   N N N          1
APEX_030200
SYS_LOB0000071157C00005$$
                                    71158          71158 LOB                 2009-08-15 2009-08-15 2009-08-15:00:43:24
VALID   N Y N          8
^CERROR:
ORA-01013: user requested cancel of current operation

8100 rows selected.       --->此处Ctrl+C结束

---End---Good Luck---