OS: SUSE10
DBMS:Oracle11.1.0.7
查询一个表报temp 表空间相关错误，日志报tempfile 不能扩展
Current log# 2 seq# 644 mem# 0: /oradata/test02/test02/redo02.log
Tue May 14 14:29:41 2013
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Tue May 14 14:29:52 2013
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
oracle@rac1:/opt/oracle/db/diag/rdbms/test02/test02/trace> export ORACLE_SID=test02
oracle@rac1:/opt/oracle/db/diag/rdbms/test02/test02/trace> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 14 14:31:18 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
扩展临时表空间失败
SQL> alter database tempfile 1 autoextend on ;
alter database tempfile 1 autoextend on
*
ERROR at line 1:
ORA-00376: file 201 cannot be read at this time
ORA-01110: data file 201: '/oradata/test02/test02/temp01.dbf'
SQL> host
从错误日志上可能数据文件offline 引起的
oracle@rac1:/opt/oracle/db/diag/rdbms/test02/test02/trace> oerr ora 376
00376, 00000, "file %s cannot be read at this time"
// *Cause: attempting to read from a file that is not readable. Most likely
// the file is offline.
// *Action: Check the state of the file. Bring it online
oracle@rac1:/opt/oracle/db/diag/rdbms/test02/test02/trace> oerr ora 1110
01110, 00000, "data file %s: '%s'"
// *Cause: Reporting file name for details of another error
// *Action: See associated error message
oracle@rac1:/opt/oracle/db/diag/rdbms/test02/test02/trace> exit
exit
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 ONLINE
FILE# STATUS
---------- -------
12 ONLINE
13 ONLINE
14 ONLINE
15 ONLINE
16 ONLINE
17 ONLINE
18 ONLINE
19 ONLINE
20 ONLINE
21 ONLINE
22 ONLINE
FILE# STATUS
---------- -------
23 ONLINE
24 ONLINE
25 ONLINE
26 ONLINE
27 ONLINE
28 ONLINE
29 ONLINE
30 ONLINE
31 ONLINE
32 ONLINE
33 ONLINE
FILE# STATUS
---------- -------
34 ONLINE
35 ONLINE
36 ONLINE
37 ONLINE
38 ONLINE
39 ONLINE
40 ONLINE
41 ONLINE
41 rows selected.
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select file_id,status from dba_data_files;
FILE_ID STATUS
---------- ---------
4 AVAILABLE
3 AVAILABLE
2 AVAILABLE
1 AVAILABLE
5 AVAILABLE
6 AVAILABLE
7 AVAILABLE
8 AVAILABLE
9 AVAILABLE
10 AVAILABLE
11 AVAILABLE
FILE_ID STATUS
---------- ---------
12 AVAILABLE
13 AVAILABLE
14 AVAILABLE
15 AVAILABLE
16 AVAILABLE
17 AVAILABLE
18 AVAILABLE
19 AVAILABLE
20 AVAILABLE
21 AVAILABLE
22 AVAILABLE
FILE_ID STATUS
---------- ---------
23 AVAILABLE
24 AVAILABLE
25 AVAILABLE
26 AVAILABLE
27 AVAILABLE
28 AVAILABLE
29 AVAILABLE
30 AVAILABLE
31 AVAILABLE
32 AVAILABLE
33 AVAILABLE
FILE_ID STATUS
---------- ---------
34 AVAILABLE
35 AVAILABLE
36 AVAILABLE
37 AVAILABLE
38 AVAILABLE
39 AVAILABLE
40 AVAILABLE
41 AVAILABLE
41 rows selected.
SQL>
SQL> select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/oradata/test02/test02/temp01.dbf
1 TEMP AVAILABLE
SQL> select status from dba_temp_files;
STATUS
---------
AVAILABLE
SQL> desc V$DATAFILE;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE
SQL> select file#,status from V$DATAFILE;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 ONLINE
FILE# STATUS
---------- -------
12 ONLINE
13 ONLINE
14 ONLINE
15 ONLINE
16 ONLINE
17 ONLINE
18 ONLINE
19 ONLINE
20 ONLINE
21 ONLINE
22 ONLINE
FILE# STATUS
---------- -------
23 ONLINE
24 ONLINE
25 ONLINE
26 ONLINE
27 ONLINE
28 ONLINE
29 ONLINE
30 ONLINE
31 ONLINE
32 ONLINE
33 ONLINE
FILE# STATUS
---------- -------
34 ONLINE
35 ONLINE
36 ONLINE
37 ONLINE
38 ONLINE
39 ONLINE
40 ONLINE
41 ONLINE
41 rows selected.
没有发现offline 的文件，果断重建临时表空间.业务需要，时间比较紧
SQL> create temporary tablespace temp2 TEMPFILE '/oradata/test02/test02/temp02.dbf'
2 SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
Tablespace created.
改defualt temp tablespace
SQL> alter database default temporary tablespace temp2;
Database altered.
删除老的temp 表空间
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
正常
----The end ------