临时表空间重建

最新推荐文章于 2022-10-11 11:16:58 发布

大枫树林

最新推荐文章于 2022-10-11 11:16:58 发布

阅读量504

点赞数

分类专栏： troube shooting

本文链接：https://blog.youkuaiyun.com/yobyin/article/details/8926246

版权

troube shooting 专栏收录该内容

18 篇文章

订阅专栏

本文记录了Oracle数据库中临时表空间无法扩展的问题排查过程，包括错误信息解析、状态检查及最终通过重建临时表空间解决问题的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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
---------- -------
        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.

增加temp 表空间

alter tablespace temp add tempfile '/oradata/mos5200/temp03.dbf ' size 32000M

正常