环境:
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;