ORA-01652: unable to extend temp segment by 8192...

      最近在rebuild index时提示unable to extend temp segment by 8192 in tablespace..的错误提示。这个是个比较常见的错误。索引在创建的时候需要使用到该用户缺省的临时表空间进行排序,以及在索引表空间生成临时段。如果当前的索引表空间限制了自动扩展或者已经达到了数据文件的最大值,此错误提示便会出现。下面是具体的分析及其解决过程。

1、错误提示信息

alter index err ORA-01652: unable to extend temp segment by 8192 in tablespace
GX_ARCHIVE_IDX
DECLARE
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8192 in tablespace GX_ARCHIVE_IDX
ORA-06512: at line 90

#下面的信息来自alert log
Sun Mar 30 03:08:51 2014
ORA-1652: unable to extend temp segment by 128 in tablespace                 GX_ARCHIVE_IDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 GX_ARCHIVE_IDX

#故障环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> ho cat /etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP4  (x86_64) - Kernel \r (\l).

2、关于ORA-1652错误 Error:  ORA-1652 Text:   unable to extend temp segment by %s in tablespace %s ------- ----------------------------------------------------------------------- Cause:  Failed to allocate an extent for temp segment in tablespace. Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more         files to the tablespace indicated or create the object in another         tablespace.

*** Important: The notes below are for experienced users - See Note:22080.1

Explanation:         This error is fairly self explanatory - we cannot get enough space for a temporary segment.         The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

  NOTE: A "temp segment" is not necessarily a SORT segment in a temporary tablespace.         It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.         eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data.               This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation.               It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend      it report ORA-1652 rather than an INDEX related space error.

临时段被使用的情形 A TEMPORARY segment may be from: 1) A SORT                  Used for a SELECT or for DML/DDL 2) CREATE INDEX          The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX  tablespace.                             Once the index build is complete the segment type is changed. 3) CREATE PK CONSTRAINT   4) ENABLE CONSTRAINT   5) CREATE TABLE              New tables start out as TEMPORARY segments.            Eg: If MINEXTENTS is > 1 or you issue CREATE table as SELECT. 6) Accessing a GLOBAL TEMPORARY TABLE   When you access a global temporary table a TEMP segment is instantiated to hold the temporary data. 

3、TROUBLESHOOTING ORA-01652(Reference Doc ID 1267351.1)

#下面是无法扩展临时段的2种情形 EXAMPLE 1:

Temporary tablespace TEMP is being used and is 50gb in size (a recommended minimum for 11g)

TIME 1 : Session 1 starts a long running query TIME 2 : Session 2 starts a query and at this point in time Session 1 has consumed 48gb of TEMP's free space TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of of its free space     Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse) TIME 4 : SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage) TIME 5 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE and V$SORT_SEGMENT ... and it is found that no space is being used (this is normal)

EXAMPLE 2:

Permanent tablespace INDEX_TBS is being used and has 20gb of space free #此时无法扩展临时表空间的问题当属第2种情形

TIME 1 : Session 1 begins a CREATE INDEX command with the index stored in INDEX_TBS TIME 2 : Session 1 exhausts all of the free space in INDEX_TBS as a result the CREATE INDEX abends TIME 3 : SMON cleans up the temporary segments that were used to attempt to create the index TIME 4 : Queries are run against the views V$SORTSEG_USAGE or V$TEMSEG_USAGE ... and it is found that the INDEX_TBS has no space used (this is normal)

#下面是Solution部分 First it is important to forget what is known about past behavior of the instance as the current tablespace size is insufficient to handle the demand by current sessions

There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space                (discuss this with a System Administrator)

    After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage

        (This is the most recommended method as it allows the database instance to find its own high watermark)

2) Monitor the temporary segment usage via queries like

           SELECT sum(blocks)*<block size of the temporary tablespace>            FROM v$tempseg_usage            WHERE tablespace = '<name of the temporary tablespace>';

     and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted

3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2

Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space

        For example: How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)         This note was written to monitor temporary tablespaces .. but may be able to be modified to also monitor permanent tablespaces

4、本案例故障解决方案  

SQL> @temp_sort_segment.sql

+==================================================================================+
| Segment Name            : The segment name is a concatenation of the             |
|                           SEGMENT_FILE (File number of the first extent)         |
|                           and the                                                |
|                           SEGMENT_BLOCK (Block number of the first extent)       |
| Current Users           : Number of active users of the segment                  |
| Total Temp Segment Size : Total size of the temporary segment in bytes           |
| Currently Used Bytes    : Bytes allocated to active sorts                        |
| Extent Hits             : Number of times an unused extent was found in the pool |
| Max Size                : Maximum number of bytes ever used                      |
| Max Used Size           : Maximum number of bytes used by all sorts              |
| Max Sort Size           : Maximum number of bytes used by an individual sort     |
| Free Requests           : Number of requests to deallocate                       |
+==================================================================================+

    Tablespace  Segment Current       Total Temp        Currently Pct.   Extent              Max         Max Used         Max Sort     Free
          Name     Name   Users     Segment Size       Used Bytes Used     Hits             Size             Size             Size Requests
-------------- -------- ------- ---------------- ---------------- ---- -------- ---------------- ---------------- ---------------- --------
TEMP           SYS.0.0        0   29,570,891,776                0    0   17,230   29,570,891,776   29,570,891,776   29,569,843,200        0
GOEX_TEMP      SYS.0.0       12   24,135,073,792       12,582,912    0  214,932   24,135,073,792    4,908,384,256    2,960,130,048        0
**************          ------- ---------------- ----------------      -------- ---------------- ---------------- ---------------- --------
sum                          12   53,705,965,568       12,582,912       232,162   53,705,965,568   34,479,276,032   32,529,973,248        0
--从上面的查询中可知,当前实例的temp临时表空间曾耗用量达到29,570,891,776,等于Total Temp Segment Size
--当前我们使用sys帐户来rebulid index,sys帐户使用的是默认的临时表空间temp。

SQL> @temp_sort_users.sql  -->这个查询是查询当前哪些session正在使用临时段,其结果与上面的一致为12,582,912

Tablespace Name Username           SID   Serial# Contents  Segment Type  Extents   Blocks        Bytes
--------------- --------------- ------ --------- --------- ------------ -------- -------- ------------
GOEX_TEMP       GOEX_WEBUSER      1079     39023 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1078     22320 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1075     15301 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1056     22505 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1046     17617 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1042     30925 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1041     10180 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1038     20315 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1034     19147 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1028      6362 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1027     12614 TEMPORARY LOB_DATA            1      128    1,048,576
                GOEX_WEBUSER      1022     23077 TEMPORARY LOB_DATA            1      128    1,048,576
***************                                                         -------- -------- ------------
sum                                                                           12    1,536   12,582,912

--那我们来看看GX_ARCHIVE_IDX表空间上索引的情形
SQL> SELECT *
  2  FROM (  SELECT segment_name, bytes / 1024 / 1024 / 1024 AS size_g, extents
  3          FROM dba_segments
  4          WHERE tablespace_name = 'GX_ARCHIVE_IDX'
  5          ORDER BY 2 DESC) t
  6  WHERE ROWNUM < 3;

SEGMENT_NAME                                                         SIZE_G  Extents
----------------------------------------------------------------- ---------- --------
PK_ACC_POS_STOCK_ARCH_TBL                                         25.9765625      540
PK_ACC_POS_CASH_PL_ARCH_TBL                                       3.97167969      177
--上面的这个查询尽然有一个接近26GB的大索引,问题应该是由于这个大索引引起的。至于这个这么大的索引是另外一个话题,不再次描述。
--根据当前的临时表空间的情形来看应该是够的。
--查看前面描述的 临时段被使用的情形2 CREATE INDEX部分在INDEX  tablespace上也会有temp segment
--所以alert日志报告无法在GX_ARCHIVE_IDX 上extend temp segment

SQL> @tbs_free_single.sql
Enter value for input_tablespace_name: GX_ARCHIVE_IDX
old  22: AND T.TABLESPACE_NAME=upper('&input_tablespace_name')
new  22: AND T.TABLESPACE_NAME=upper('GX_ARCHIVE_IDX')

TABLESPACE_NAME                USED_MB  FREE_MB  TOTAL_MB PER_FR
------------------------------ -------- -------- -------- ------
GX_ARCHIVE_IDX                 45,912   19,037   64,949   29 %

SQL> @tbs_free_by_file_id.sql
Enter value for input_tbsname: GX_ARCHIVE_IDX
old  26:        AND t.tablespace_name = UPPER ('&input_tbsname')
new  26:        AND t.tablespace_name = UPPER ('GX_ARCHIVE_IDX')

TABLESPACE_NAME                   FILE_ID USED_MB  FREE_MB  TOTAL_MB PER_FR
------------------------------ ---------- -------- -------- -------- ------
GX_ARCHIVE_IDX                       25   29,328    2,916   32,244    9 %
GX_ARCHIVE_IDX                       40   16,584   16,121   32,705   49 %

SQL> select file_id,file_name,autoextensible from dba_data_files where file_id in(25,40);

   FILE_ID FILE_NAME                                                    AUT
---------- ------------------------------------------------------------ ---
        25 /u02/database/CABO3/oradata/CABO3_archive_idx.dbf            NO
        40 /u02/database/CABO3/oradata/CABO3_archive_idx2.dbf           YES

--根据1267351.1的solution,我们为GX_ARCHIVE_IDX表空间添加一个新的数据文件
SQL> alter tablespace GX_ARCHIVE_IDX add datafile '/u02/database/CABO3/oradata/CABO3_archive_idx3.dbf'
  2  size 2g autoextend on;

Tablespace altered.

--为该表空间增加数据文件后,无此异常

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

9860
来自专栏Netkiller

数据库安全·开发加密插件

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

33470
来自专栏deed博客

Oracle在Linux下安装

26420
来自专栏数据库新发现

字符集问题的初步探讨(二)

原文发表于itpub技术丛书《Oracle数据库DBA专题技术精粹》,未经许可,严禁转载本文.

13820
来自专栏乐沙弥的世界

Linux/Unix shell 自动导入Oracle数据库

      使用shell脚本实现对Oracle数据库的监控与管理将大大简化DBA的工作负担,如常见的对实例的监控,监听的监控,告警日志的监控,以及数据库的备份...

25320
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

39130
来自专栏大内老A

谈谈基于SQL Server 的Exception Handling[中篇]

三、TRY CATCH & Return 在上面一节中,我通过RAISERROR重写了创建User的Stored procedure,实际上上面的Stored ...

19560
来自专栏杨建荣的学习笔记

impdp ORA-39002,ORA-39166,ORA-39164的问题及解决(r2第6天)

今天在做imp和impdp的性能测试时,发现如果表中存在lob字段,加载真是慢的厉害,每秒钟大概1000条的样子,按照这种速度,基本上不用干活了。 比如5千万条...

33970
来自专栏杨建荣的学习笔记

sed+awk模拟简单sql查询(26天)

经常需要用sqlplus去查询一些数据字典类型的数据,这些数据量不大,而且需要环境之间都是一样的,所以就想使用离线查询的方式,把那些数据以平面文件的形式存放,...

371120
来自专栏杨建荣的学习笔记

巧用shell脚本分析数据库用户(r2第4天)

在数据库维护中,可能对于一个陌生的schema,需要了解它的一些情况,可以使用如下的脚本来很快得到一个报告,里面包含了详尽的信息。 用户占用的空间,权限,角色和...

35070

扫码关注云+社区

领取腾讯云代金券