troubleshooting ora-1652 unable to extend temp segment

troubleshooting ora-1652 unable to extend temp segment
1.错误信息
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
从报错信息来看,1652错误是由于无法在某个表空间中扩展临时段导致
诊断步骤:
a.首先从报错的表空间中查看是否为临时表空间,如果不是临时表空间,那么说明
是表空间不足导致的,可以加大表空间的大小,在非临时表空间产生临时段的原
因
1)
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.
2)
CREATE PK CONSTRAINT
3)
ENABLE CONSTRAINT
4)
CREATE TABLE New tables start out as TEMPORARY segments.
Eg: If MINEXTENTS is > 1 or you issuse CREATE table as SELECT.

b.临时表
临时表在创建表的时候并不分配空间,而是在数据插入以后才分配空间的,如果是
因为临时表报错,可以简单的认为是临时表空间的大小不足

c.临时的lob段
临时的lob段在session断开之前即使这个临时的lob变量已经不使用了,也不会释放
,对于经常使用lob对象的程序,同时又使用连接池的应用,可能会经常出现lob对象
导致的ora-1652,在10.2.0.4以前并没有什么办法能解决这问题,只能释放session,
10.2.0.4及以上版本oracle使用一个新的event 60025来解决这问题,我在10.2.0.4
上已经做过测试
metalink:802897.1

d.排序和hash join
排序和hash join首先会在pga中进行,如果内存不足,那么就会使用到临时表空间,因此
要首先检查内存设置是否合理,同时对于这类问题,我们可以适当的增加临时表空间看看
能否解决这问题,但是不能盲目的一直增加临时表空间,应该首先考虑的是,排序是否有必
要,hash join是否是最好的执行计划,最好能通过一些优化手段来避免这样的问题,如通过
索引或者程序来避免不必要的排序, 或者能否通过nest loop来代替hash join

e.不正确的执行计划
上面的排序和hash join也可以归结为这一类,很多情况下都是由于不正确的执行计划(如迪卡尔积)
导致消耗了大量的临时表空间.
f.诊断手段
1)设置event errorstack
session:
alter session set events '1652 trace name errorstack level 3';
system:
ALTER SYSTEM SET EVENTS '1652 trace name errorstack';
parameter:
EVENTS='1652 trace name errorstack';
设置了errorstack后可以在遇到1652的时候,会生成一个trace文件,能够看到当时报错的sql,堆栈信息,
执行计划和相关的参数
重点搜索关键字:
current sql :获取报错的sql
plan table :报错sql的执行计划
DYNAMICALLY MODIFIED PARAMETERS:参数设置
g.通过定期捕获v$视图
可以写个job,定期执行下面的sql,并把结果写到一个表里,以便日后分析
1)
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;
2)
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/8984272/viewspace-619907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8984272/viewspace-619907/