Oracle临时表空间重建

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 tablespaceSQL> alter database default temporary tablespace temp2;

Database altered.

删除老的temp 表空间
SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.
正常
----The end ------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12统计Oracle当前用户下所有表中的记录数sysaux 表空间不足问题处理相关资讯 Oracle临时表 Oracle临时表空间

Oracle 12C RAC关于临时表空间的一（08/27/2015 16:38:26）
Oracle中临时表空间的清理（06/06/2015 06:34:17）
Oracle 锁定临时表统计信息及锁住（11/21/2014 13:58:20）

Oracle系统默认临时表空间以及redo （07/08/2015 08:41:18）
Oracle中的临时表、外部表和分区表（03/02/2015 13:36:40）
被Oracle全局临时表坑了（10/20/2014 18:40:25）

本文评论查看全部评论（0）