环 境:Linux 5.5 x86-64 + Oracle 11.2.0.3 RAC
问题描述:
########
alert日志
########
Tue Jul 16 13:02:41 2013
ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA
Fri Jul 26 13:03:06 2013
ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA
Sun Aug 04 01:03:45 2013
ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA
####################参考AskTom.oracle.com
####################
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:36859040165792
1,temp segment怎么来的?
when you create a new segment, Oracle uses temporary extents to initially build it and
then at the end of the process -- converts (via a simple dictionary update) the extents
into permanent ones.
2,碎片可能性
a) dictionary managed tablespace
b) that has lots of free extents (many extents in dba_free space)
c) that are not contigous
d) so the sum of free space is 25% of the tablespace
e) BUT the largest contigous set of free space is not large enough for your next extent of the ind
########参考网友
########
http://www.dbasupport.com/forums/showthread.php?47973-ORA-1652-unable-to-extend-temp-segment
指出temp segment不一定是在临时表空间产生的。
Temp segment is not in the 'TEMP' tablespace.
It has to do with freespace available in the tablespace that is listed (not given in the original post).
Extend the tablespace you are building the object in.
参考网友 http://blog.csdn.net/mrluoe/article/details/8224848 该文认为是BUG
参考MOS How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP (Doc ID 1326118.1) 该文认为是BUG
参考MOS OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s (Doc ID 19047.1) 该文对temp segment ORA-1652进行解释
#########################
查看问题时间点的ADDM,AWR报告
#########################
1,SQL_ID=26gwqv3ahtw1r与SQL_ID=gt9rkpq3m1qp3在使用"APPS_TS_MEDIA"表空间,这2个SQL导致ORA-1652错误。
create table "APPLSYS".DBMS_TABCOMP_TEMP_UNCMP tablespace "APPS_TS_MEDIA"
nologging
as
select /*+ DYNAMIC_SAMPLING(0)FULL("APPLSYS"."FND_LOBS") */ * from "APPLSYS"."FND_LOBS" sample block( 18.54) mytab;
create table "APPLSYS".DBMS_TABCOMP_TEMP_CMP organization heap tablespace "APPS_TS_MEDIA"
compress for all operations
nologging
as
select /*+ DYNAMIC_SAMPLING(0) */ * from "APPLSYS".DBMS_TABCOMP_TEMP_UNCMP mytab;
2,为什么这个SQL语句会导致ORA-1652呢?因为create table as select语句会使用temp segment。3,使用Tom的free.sql脚本查看可以使用的总空间,及最大的连续的空间,都是充足的。
Tablespace Name KBytes Used Free Used(%) Largest
APPS_TS_MEDIA 4,194,304 3,135,232 1,059,072 74.7 1,059,072
4,为什么空间充足,却有报错???是BUG还是???近期这个问题又发生了,在之前分析的基础上继续思考这个问题。
下面是几点总结:
a, 这个ORA-1652发生的时间正好是13:00系统自动维护任务窗口,自动任务包含3个:统计数据收集,段诊断,SQL Tuning。
b, 根据AWR与ORA-1652中报错的表空间APPS_TS_MEDIA,找到了:
2个涉及的SQL语句与临时对象DBMS_TABCOMP_TEMP_UNCMP, DBMS_TABCOMP_TEMP_CMP,
1个涉及的基表FND_LOBS
c, 结合a,b两点, 发现这2个对象是(segment advisor)段诊断中的Compression Advisor必须的过程。
这点请参考MOS: How Does Compression Advisor Work? (文档 ID 1284972.1)
d,考察FND_LOBS,发现这个表含有LOB大对象类型的数据。
查询发现,这个表是Oracle EBS中最大的一个对象,这点可以参考网友http://oracleappstechnology.blogspot.hk/2009/02/whats-inside-fndlobs.html。
另外查询这个表及LOB对象的关系及大小,这点可以参考网友http://appsoracle.blogspot.hk/2012/09/information-on-fndlobs.html。
最终发现APPS_TS_MEDIA表空间为4096M,而这个表及LOB对象占用了近3.3GB。
e,得出结论:自动维护任务之段诊断的-Compression Advisor,因空间不足,无法创建该任务需要的(临时)表
DBMS_TABCOMP_TEMP_UNCMP, DBMS_TABCOMP_TEMP_CMP, 并导致了ORA-1652。
相关SQL如下:
SQL> select owner,segment_name,segment_type,bytes from dba_segments where tablespace_name='APPS_TS_MEDIA' and OWNER='APPLSYS'
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------------------ ------------------ ----------
APPLSYS FND_LOBS TABLE 88342528
APPLSYS FND_LOBS_U1 INDEX 1703936
APPLSYS SYS_IL0000034032C00004$$ LOBINDEX 2228224
APPLSYS FND_LOBS_N1 INDEX 2752512
APPLSYS SYS_LOB0000034032C00004$$ LOBSEGMENT 3281649664
SQL> select owner,table_name,segment_name from dba_lobs where table_name='FND_LOBS';
OWNER TABLE_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------
APPLSYS FND_LOBS SYS_LOB0000034032C00004$$
SQL> select sum(bytes), s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'FND_LOBS'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;
SUM(BYTES) SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------------
3281649664 SYS_LOB0000034032C00004$$ LOBSEGMENT