ORA-01652 无法通过128 在表空间 TEMP中 扩展temp段 剖析解决

               

 

 

 

 

 

1，同事说执行sql报错

同事在plsql里面执行sql报错，报错信息：ora-01652 无法通过128 (在表空间 TEMP中)扩展temp段，如下图所示：

 

 

2，查看报错sql语句

Sql比较长，而且无法扩展temp字段，那么基本推断可能有如下2种情况：

（1）oracle的temp临时表空间太小了；

（2）一个性能非常差的笛卡尔积的带全表扫描的sql占用的资源超过了temp的表空间大小。

先看执行的sql语句，sql比较长，所以这种属于（1）（2）的结合情况了，sql如下：

select p.project_cd,          b.budget1,          b.budget2,          b.budget3,          b.budget4,          b.budget5,          b.budget6,          b.budget7,          b.budget8,          b.budget9,           b.budget10,           b.budget11,           b.budget12,           b.pledge_budget1,           b.pledge_budget2,           b.pledge_budget3,           b.pledge_budget4,           b.pledge_budget5,           b.pledge_budget6,           b.pledge_budget7,           b.pledge_budget8,           b.pledge_budget9,           b.pledge_budget10,           b.pledge_budget11,           b.pledge_budget12,          x.aa,          v.dd,          v.ee,          v.ff,          i.gg,          i.hh,          i.ii,          u.jj,          y.mm,          y.nn,          y.oo,          y.pp,           l.plan_collection_id,           l.cost_total,          l.cost_12,          l.cost_25,           l.approve_total,           l.approve_12,           l.approve_25,           l.accumulative_no,          l.property_plan,          l.approve,          l.remark,           l.month_steel_pay,           l.month_strategy_pay,           p.project_name,           l.plan_collection_status,          w1,          ac.dfk,           tt.manual_pay_num,           ttt.sf_money,          x2.sf_dfk,           sf.sfmoney,          y2.qq,           p.budget_order,           uu1.yfdikuan,           k1.lastMonthDfk,          p.is_home,           p.actualpay1508,           p.pledgeactualplan1508     from cont_project_code  p     leftjoin project_budget b       on p.project_cd =  b.project_cd      and b.year = '2016'     leftjoin  budget_month_plan_collection l       on l.project_cd =  p.project_cd      and l.year = '2016'      and l.month = '6'      and l.is_marketing isnull     leftjoin (select p.project_cd as projectcd, sum(a.sf_money) as aa                  from cont_actualpay a                  leftjoin cont_ledger l                    on l.cont_ledger_id  = a.cont_ledger_id                  leftjoin cont_project_code  p                    on p.project_cd =  l.project_cd                 where l.enable_flg = 1                   and a.sp_date <  to_date('2016-01-01', 'yyyy-mm-dd')                   andnvl(l.cont_type_cd2, 0) <> 9                   andnvl(l.tex_cont_flg, 0) <> 1                   andnvl(l.is_finance, 0) <> 1                 groupby p.project_cd) x       on x.projectcd =  p.project_cd     leftjoin (select p.project_cd