环境：

os:RHEL6.4 X86_64

oracle software:ORACLE11.2.0.4

scheme:cwbase1_9999

步骤：

1.SQL> select TABLE_NAME from user_tables;

no rows selected

2.

SQL> select USERNAME,DEFAULT_TABLESPACE ,TEMPORARY_TABLESPACE from dba_users where USERNAME='CWBASE1_9999';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
CWBASE1_9999 CWDATA1
CWTEMP1

3.SQL> select tablespace_name,FILE_NAME from dba_temp_files;

CWTEMP1

/u01/app/oracle/oradata/cnpc/CWTEMP1.dbf

4.使用root用户

rm -rf /u01/app/oracle/oradata/cnpc/CWTEMP1.dbf

5.生成大量数据，使用排序来使用临时表空间

SQL> create table hahaha as select * from all_objects;
insert into hahaha select * from hahaha;

Table created.

SQL>
81631 rows created.

SQL> /

163262 rows created.

SQL> /

326524 rows created.

6.SQL> select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13
*
ERROR at line 1:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u01/app/oracle/oradata/cnpc/CWTEMP1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

7.alter tablespace cwtemp1 add tempfile '/u01/app/oracle/oradata/cnpc/CWTEMP1_1.dbf' size 10m;

8.SQL> select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13
*
ERROR at line 1:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u01/app/oracle/oradata/cnpc/CWTEMP1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

因为系统记录着之前的文件信息。

9.alter tablespace cwtemp1 drop tempfile '/u01/app/oracle/oradata/cnpc/CWTEMP1.dbf';

10.再次排序，由于没有使用数据文件自动扩展，现有空间不足以存储排序的数据，再次报错。

SQL> select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
select * from hahaha order by 1,2,3,4,5,6,7,8,9,10,11,12,13
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace CWTEMP1

11.修改自动扩展，select成功。

alter database tempfile '/u01/app/oracle/oradata/cnpc/CWTEMP1_1.dbf' autoextend on;