ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA - 问题记录

环      境：Linux 5.5 x86-64 + Oracle 11.2.0.3 RAC
问题描述：
########
alert日志
########

Tue Jul 16 13:02:41 2013ORA-1652: unable to extend temp segment by 16 in tablespace                 APPS_TS_MEDIAFri Jul 26 13:03:06 2013ORA-1652: unable to extend temp segment by 16 in tablespace                 APPS_TS_MEDIASun Aug 04 01:03:45 2013ORA-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 tablespaceb) that has lots of free extents (many extents in dba_free space)c) that are not contigousd) so the sum of free space is 25% of the tablespacee) 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"nologgingasselect /*+ 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 operationsnologgingasselect /*+ 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                88342528APPLSYS                        FND_LOBS_U1                    INDEX                 1703936APPLSYS                        SYS_IL0000034032C00004$$       LOBINDEX              2228224APPLSYS                        FND_LOBS_N1                    INDEX                 2752512APPLSYS                        SYS_LOB0000034032C00004$$      LOBSEGMENT         3281649664SQL> 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_typefrom dba_lobs l, dba_segments swhere s.segment_type = 'LOBSEGMENT'and l.table_name = 'FND_LOBS'and s.segment_name = l.segment_namegroup by s.segment_name,s.segment_type;SUM(BYTES) SEGMENT_NAME                   SEGMENT_TYPE---------- ------------------------------ ------------------3281649664 SYS_LOB0000034032C00004$$      LOBSEGMENT