A-A+

oracle 用户临时表空间数据文件丢失的处理

2015年03月29日 Oracle 暂无评论 阅读 4,923 次

环境:

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;

给我留言