oracle 用户临时表空间数据文件丢失的处理
环境：
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;