数据库遇到ORA-01652: 无法通过128（在表空间TEMP中）扩展 temp 段 问题，请高手帮助！！

Question

ORA-01652: 无法通过128（在表空间TEMP中）扩展 temp 段  该怎么办？select distinct A.object_id,A.objectname,A.object_code,C.org_name, A.rec_type,A.sex,A.manglevel,A.accusal,A.state,A.is_pos,A.enter_date,A.unrec_date, case when E."01" is null then 0 else E."01" end "01", case when E."02" is null then 0 else E."02" end "02", case when E."03" is null then 0 else E."03" end "03", case when E."04" is null then 0 else E."04" end "04", case when E."05" is null then 0 else E."05" end "05", case when E."06" is null then 0 else E."06" end "06", case when E."07" is null then 0 else E."07" end "07", case when E."08" is null then 0 else E."08" end "08", case when E."09" is null then 0 else E."09" end "09", case when E."10" is null then 0 else E."10" end "10", case when E."11" is null then 0 else E."11" end "11", case when E."12" is null then 0 else E."12" end "12"  from t_month_assess B right join t_rec_object A on A.object_id = B.object_id 　left join t_org C on B.org_id = C.org_id　 left join (select * from (  select distinct object_id,sum((decode(substrb(assess_month,5,2),'01',collgt_score,0))) "01" ,  sum((decode(substrb(assess_month,5,2),'02',collgt_score,0))) "02" , sum((decode(substrb(assess_month,5,2),'03',collgt_score,0))) "03" , sum((decode(substrb(assess_month,5,2),'04',collgt_score,0))) "04" , sum((decode(substrb(assess_month,5,2),'05',collgt_score,0))) "05" , sum((decode(substrb(assess_month,5,2),'06',collgt_score,0))) "06" , sum((decode(substrb(assess_month,5,2),'07',collgt_score,0))) "07" , sum((decode(substrb(assess_month,5,2),'08',collgt_score,0))) "08" , sum((decode(substrb(assess_month,5,2),'09',collgt_score,0))) "09" , sum((decode(substrb(assess_month,5,2),'10',collgt_score,0))) "10" , sum((decode(substrb(assess_month,5,2),'11',collgt_score,0))) "11" , sum((decode(substrb(assess_month,5,2),'12',collgt_score,0))) "12" from t_month_assess where substrb(assess_month,1,4)='2010' group by object_id  )) E on B.object_id =E.object_id 就是在这条sql运行时出现的，上面的12个case when 任意去掉一个就可以，这是什么原因，解决办法？？

Answer 1

这种问题一般有两种原因：一是临时表空间空间太小，二是不能自动扩展。
select * from dba_tablespaces;
select * from dba_temp_files;
select * from v$tempfile
看一下你的临时表空间的信息。看你的SQL语句，估计是临时表空间不足了。

Answer 2

INSERT /*+ append */
  INTO IMEINEWINFO
    (MONTH,
     PHONE,
     BRAND_CH,
     productname,
     opsystem,
     opsystemversion,
     DEVICE_ID,
     DEVICE_NAME,
     iscustom,
     isfocus)
    select pr.month,
           io.phone,
           pr.brand_ch,
           pr.productname,
           pr.opsystem,
           pr.opsystemversion,
           pr.device_id,
           pr.device_name,
           pr.iscustom,
           pr.isfocus
      from imei_changeinfo io
     inner join (select t.productmodelid, t.imeisect, t.sort
                   from imei_productimeiinfo t
                  where t.sort =
                        (select min(sort)
                           from imei_productimeiinfo
                          where imei_productimeiinfo.imeisect = t.imeisect)) ip on substr(io.new_imei,
                                                                                          1,
                                                                                          8) =
                                                                                   ip.imeisect
     inner join newchangeinfo pr on pr.productmodelid =
                                       ip.productmodelid
     where pr.productid =
           (select max(productid)
              from product
             where productmodelid = ip.productmodelid)
我也是这样。不明白为什么。