ORA-01652:unable to extend temp segment by num in tablespace name

今天在DataStage中出现这个错误：

F_APP_NB_L1,0: Oracle call failed; sqlcode = -1,652; message: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

 

此方面相关的知识，可看看： http://blog.csdn.net/lanmao100/archive/2010/07/13/5730979.aspx

 

出问题的SQL是这个:

select  t.*
  from F_APP_NB_L1 t
      ,(select a.appno ,a.classcode
              ,max(a.begtime) begtime
          from F_APP_NB_L1 a
         where a.endtime = '99991231999'
           and exists (select *
                  from F_APP_NB_L1 b
                 where b.appno = a.appno
                   and b.classcode = a.classcode
                   and b.endtime < '99991231999')
         group by a.appno,a.classcode) c
 where c.appno = t.appno and c.classcode = t.classcode
   and c.begtime = t.begtime ;

 

后改为：

with temp_nb as
(select a.appno ,a.classcode
              ,max(a.begtime) begtime
          from F_APP_NB_L1 a
         where a.endtime = '99991231999'
           and exists (select *
                  from F_APP_NB_L1 b
                 where b.appno = a.appno
                   and b.classcode = a.classcode
                   and b.endtime < '99991231999')
         group by a.appno,a.classcode)
select  t.*
  from F_APP_NB_L1 t
      ,temp_nb c
 where c.appno = t.appno and c.classcode = t.classcode
   and c.begtime = t.begtime

 

这样，etl就不报错了，同时速度快了很多。