ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

原创 Linux操作系统 作者:rocky_gao 时间:2011-12-14 20:24:29 0 删除 编辑

“ORA-1652: unable to extend temp segment”

       临时表空间被资源中的多个会话共享,并且quotas不能限制每个用户使用的临时表空间数量,当临时表空间被填充满时,任何尝试获得更多的临时表空间的用户将会得到“ORA-1652: unable to extend temp segment”错误。

 

解决办法:
1、扩大临时tablespace
2、调整sql语句或者创建index等,减少临时tablespace的使用

 

Oracle排序的基础

       Oracle会话首先在内存中进行排序,当Oracle需要存储数据到临时表或者为哈西排序建立哈希表时,并且也会首先在内存中进行操作,虽然这两个操作不需要排序操作,但是它们在Oracle中的处理方式是相同的。

       如果操作使用内存超过了阈值,Oracle会将操作分为多个较小的操作以使每个可以在内存中操作。部分结果将会被写入磁盘的临时表空间,任何一个会话可以使用的内存数依赖于初始化参数的设置,如果workarea_size_policyauto,则由pga_aggregate_target控制,否则由sort_area_size, hash_area_size,和bitmap_merge_area_size控制内存的使用。

       当排序操作太大以至于不能在内存中执行时,Oracle将在临时表空间中分配空间以执行操作。临时段在临时表空间中也称为“排序段”,sys拥有,而不是执行排序操作的用户。通常每个表空间中只有一个排序段,因为多个会话可以共享排序段,用户使用临时表空间不需要在其上有quota,事实上会被Oracle忽略。

       临时表空间中只能包含临时段,因此临时段上的操作不会产生undoredo,同时分配临时段给用户也不需要记录在dd或位图块上。因为临时表空间不会超过创建它的会话的生命周期。

       一个SQL可以有多个排序操作,一个数据库会话同时可以有多个活动的SQL,当到磁盘上的排序结果不再需要时,其在排序段中的块会标记为不再使用并可以被分配给其他排序操作。

       如果发生以下情况排序操作将会失败:排序段中没有不再使用的块;临时表空间中没有空间可以供排序段分配额外的分区。这在大多数情况下会导致语句发生以下错误:“ORA-1652: unable to extend temp segment.”并记录在实例的alert log中。

       不过需要注意的是ORA-1652并不全部指示临时表空间问题,ALTER TABLE…MOVE也会发生该错误,如果目标表空间没有足够的空间容纳移动的表空间。

 

识别由于缺少临时表空间失败的SQL语句

       虽然Oracle logs ORA-1652错误到警告日志中通知dba发生了空间问题,但是Oracle不会识别那条错误的语句。

       可以使用Oracle诊断事件跟踪ORA-1652事件,该诊断事件的影响很小,仅在发生ORA-1652错误时才会写入信息。

       ALTER SESSION SET EVENTS '1652 trace name errorstack';

       在会话范围内设置;

       ALTER SYSTEM SET EVENTS '1652 trace name errorstack';

       永久性设置:

       ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;

       还可以在其他会话内使用“oradebug event”进行跟踪。

 

       可以使用以下语句关闭:

ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';

ALTER SYSTEM SET EVENTS '1652 trace name context off';

ALTER SESSION SET EVENTS '1652 trace name context off';

如果一个SQL语句由于缺少临时表空间失败并且ORA-1652诊断事件已经激活,那么

Oracle服务器进程将会在遇到错误时在user_dump_dest目录的跟踪文件写入错误信息,并且警告日志会指示出相关跟踪文件。如:

Tue Jan  2 17:21:14 2007

Errors in file

/u01/app/oracle/admin/rpkprod/udump/rpkprod_ora_10847.trc: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

       跟踪文件中将包含类似如下的信息:

Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_2

System name:    SunOS

Node name:      rpk

Release:        5.8

Version:        Generic_108528-27

Machine:        sun4u

Instance name: rpkprod

Redo thread mounted by this instance: 1

Oracle process number: 18

Unix process pid: 10847, image: oracle@rpk (TNS V1-V3)

 

*** ACTION NAME:() 2007-01-02 17:21:14.871

*** MODULE NAME:(SQL*Plus) 2007-01-02 17:21:14.871

*** SERVICE NAME:(SYS$USERS) 2007-01-02 17:21:14.871

*** SESSION ID:(130.13512) 2007-01-02 17:21:14.871

*** 2007-01-02 17:21:14.871

ksedmp: internal or fatal error

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Current SQL statement for this session:

SELECT   "A1"."INVOICE_ID", "A1"."INVOICE_NUMBER", "A1"."INVOICE_DAT

E", "A1"."CUSTOMER_ID", "A1"."CUSTOMER_NAME", "A1"."INVOICE_AMOUNT",

 "A1"."PAYMENT_TERMS", "A1"."OPEN_STATUS", "A1"."GL_DATE", "A1"."ITE

M_COUNT", "A1"."PAYMENTS_TOTAL"

FROM     "INVOICE_SUMMARY_VIEW" "A1"

ORDER BY "A1"."CUSTOMER_NAME", "A1"."INVOICE_NUMBER"

----- Call Stack Trace -----

虽然使用这种方法可以得到相当详细的信息,但是需要注意的是,这种方法捕获到的语

句并不一定是问题的根源,因为有可能前一个语句消耗了99.9%临时空间,而第二个语句被捕获到跟踪文件中。

       跟踪文件同时还会包含如调用栈跟踪和二进制栈dump,该信息通常没有价值,除非想要了解Oracle内部。

       通常不应该在实例级别设置该诊断事件。如果经常在批处理期间遇到该错误,可以在批处理开始设置alter session进行会话级跟踪。

 

监控临时表空间

       可以在发生错误前实时监控数据库中临时表空间的使用情况,以避免出现错误。任何时候,Oracle都可以告诉dba数据库中的临时表空间,会话使用的排序空间,以及语句使用的排序空间。所有这些信息都可以通过v$得到。

临时段

       Oracle会在第一次执行磁盘排序时创建排序段,并且根据需要扩展,但是不会收缩。

SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;

 

会话使用的排序空间

SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;

 

语句使用的临时空间

SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       T.blocks * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       T.sqladdr address,
       Q.hash_value,
       Q.sql_text
  FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
 WHERE T.session_addr = S.saddr
   AND T.sqladdr = Q.address(+)
   AND T.tablespace = TBS.tablespace_name
 ORDER BY S.sid;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11976525/viewspace-713325/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ASM
请登录后发表评论 登录
全部评论

注册时间:2010-04-09

  • 博文量
    112
  • 访问量
    273977