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

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 as projectcd,

                sum(d.actual_loc_amt) as dd,

                sum(d.actual_loc_utilities_amt) as ee,

                sum(d.actual_loc_other_amt) as ff

           from cont_pay_sum_detail d

           leftjoin cont_pay_sum s

             on d.cont_pay_sum_id = s.cont_pay_sum_id

           leftjoin cont_actualpay a

             on s.cont_actualpay_id = a.cont_actualpay_id

           leftjoin cont_project_code p

             on p.project_cd = a.project_cd

          where d.is_dikuan = '0'

            and d.biz_date between to_date('2016-01', 'yyyy-mm') and

                to_date('2016-6', 'yyyy-mm')

          groupby p.project_cd) v

on v.projectcd = p.project_cd

leftjoin (select p.project_cd as projectcd,

                sum(d.actual_loc_amt) as gg,

                sum(d.actual_loc_utilities_amt) as hh,

                sum(d.actual_loc_other_amt) as ii

           from cont_pay_sum_detail d

           leftjoin cont_pay_sum s

             on d.cont_pay_sum_id = s.cont_pay_sum_id

           leftjoin cont_actualpay a

             on s.cont_actualpay_id = a.cont_actualpay_id

           leftjoin cont_project_code p

             on p.project_cd = a.project_cd

          where d.is_dikuan = '0'

            and to_char(d.biz_date, 'yyyy-mm') = '2016-5'

          groupby p.project_cd) i

on i.projectcd = p.project_cd

leftjoin (select a.project_cd as projectcd, sum(a.dfk_money1) as jj

           from cont_actualpay a, cont_ledger t

          where a.cont_ledger_id = t.cont_ledger_id

            and t.enable_flg = 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            andnvl(t.is_finance, 0) <> 1

            and a.created_date between to_date('2016-01', 'yyyy-mm') and

                to_date('2016-6', 'yyyy-mm')

          groupby a.project_cd) u

on u.projectcd = p.project_cd

leftjoin (select b.project_cd projectcd,

                sum(b.project_opinion_12) as mm,

                sum(b.project_opinion_25) as nn,

                sum(b.cost_opinion_12) as oo,

                sum(b.cost_opinion_25) as pp,

                sum(b.finance_pay) as qq

           from BUDGET_MONTH_PLAN b

          where b.cont_ledger_id <> '合计'

            and b.year = 2016

            and b.month = 6

          groupby b.project_cd) y

on y.projectcd = p.project_cd

leftjoin (select t.project_cd, sum(t4.convert_currency) as qq

           from cont_ledger         t,

                cont_actualpay      t1,

                cont_pay_sum        t2,

                cont_pay_sum_detail t3,

                cont_pay_detail     t4

          where t.cont_ledger_id = t1.cont_ledger_id

            and t1.cont_actualpay_id = t2.cont_actualpay_id

            and t2.cont_pay_sum_id = t3.cont_pay_sum_id

            and t3.cont_pay_sum_detail_id = t4.cont_pay_sum_detail_id

            and t.enable_flg = 1

            andnvl(t.is_finance, 0) <> 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            and to_char(t3.biz_date, 'yyyy-mm') = '2016-05'

            and t4.entry_outbgitem_number <> 'b.12'

          groupby t.project_cd) y2

on y2.project_cd = p.project_cd

leftjoin (select t4.project_cd, sum(t0.pay_money) as w1

           from cont_pay_detail t0

           leftjoin cont_pay_sum_detail t1

             on t1.cont_pay_sum_detail_id = t0.cont_pay_sum_detail_id

           leftjoin cont_pay_sum t2

             on t2.cont_pay_sum_id = t1.cont_pay_sum_id

           leftjoin cont_actualpay t3

             on t3.cont_actualpay_id = t2.cont_actualpay_id

           leftjoin cont_ledger t4

             on t4.cont_ledger_id = t3.cont_ledger_id

          where t0.entry_outbgitem_number = 'b.12'

            andnvl(t4.cont_type_cd2, 0) <> 9

            andnvl(t4.tex_cont_flg, 0) <> 1

            andnvl(t4.is_finance, 0) <> 1

          groupby t4.project_cd) w

on w.project_cd = p.project_cd

leftjoin (select a.project_cd as projectcd, sum(a.dfk_money1) as dfk

           from cont_actualpay a, cont_ledger t

          where a.status_flg <> '1'

            and a.cont_ledger_id = t.cont_ledger_id

            and t.enable_flg = 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            andnvl(t.is_finance, 0) <> 1

            and a.sp_date < to_date('2016-6', 'yyyy-mm')

          groupby a.project_cd) ac

on ac.projectcd = p.project_cd

leftjoin (select t.project_cd, sum(t.manual_pay_num) manual_pay_num

           from cont_ledger t

          where t.enable_flg = 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.is_finance, 0) <> 1

            andnvl(t.tex_cont_flg, 0) <> 1

          groupby t.project_cd) tt

on tt.project_cd = p.project_cd

leftjoin (select t.project_cd, sum(t1.sf_money) sf_money

           from cont_ledger t

           leftjoin cont_actualpay t1

             on t1.cont_ledger_id = t.cont_ledger_id

           leftjoin cont_pay_sum t2

             on t2.cont_actualpay_id = t1.cont_actualpay_id

          where1 = 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            andnvl(t.is_finance, 0) <> 1

            andnotexists

          (select1

                   from cont_pay_sum_detail t3

                  where t3.cont_pay_sum_id = t2.cont_pay_sum_id)

          groupby t.project_cd) ttt

on ttt.project_cd = p.project_cd

leftjoin (select p.project_cd as projectcd,

                sum(cpd.convert_currency) as sf_dfk

           from cont_pay_sum_detail d

           leftjoin cont_pay_sum s

             on d.cont_pay_sum_id = s.cont_pay_sum_id

           leftjoin cont_actualpay a

             on s.cont_actualpay_id = a.cont_actualpay_id

           leftjoin cont_ledger c

             on c.cont_ledger_id = a.cont_ledger_id

           leftjoin cont_project_code p

             on p.project_cd = c.project_cd

           leftjoin cont_pay_detail cpd

             on cpd.cont_pay_sum_detail_id = d.cont_pay_sum_detail_id

          where cpd.entry_outbgitem_number = 'b.12'

            and c.enable_flg = '1'

            and a.status_flg <> '1'

            andnvl(c.cont_type_cd2, 0) <> 9

            andnvl(c.is_finance, 0) <> 1

            andnvl(c.tex_cont_flg, 0) <> 1

            and a.sp_date < to_date('2016-6', 'yyyy-mm')

          groupby p.project_cd) x2

on x2.projectcd = p.project_cd

leftjoin (select t.project_cd, sum(t4.convert_currency) as sfmoney

           from cont_ledger         t,

                cont_actualpay      t1,

                cont_pay_sum        t2,

                cont_pay_sum_detail t3,

                cont_pay_detail     t4

          where t.cont_ledger_id = t1.cont_ledger_id

            and t1.cont_actualpay_id = t2.cont_actualpay_id

            and t2.cont_pay_sum_id = t3.cont_pay_sum_id

            and t3.cont_pay_sum_detail_id = t4.cont_pay_sum_detail_id

            and t.enable_flg = 1

            andnvl(t.is_finance, 0) <> 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            and t3.biz_date between to_date('2016-01-01', 'yyyy-MM-dd') and

                to_date('2016-6-01', 'yyyy-MM-dd')

            and t4.entry_outbgitem_number <> 'b.12'

          groupby t.project_cd) sf

on sf.project_cd = p.project_cd

leftjoin (select t.project_cd, sum(t4.convert_currency) as yfdikuan

           from cont_ledger         t,

                cont_actualpay      t1,

                cont_pay_sum        t2,

                cont_pay_sum_detail t3,

                cont_pay_detail     t4

          where t.cont_ledger_id = t1.cont_ledger_id

            and t1.cont_actualpay_id = t2.cont_actualpay_id

            and t2.cont_pay_sum_id = t3.cont_pay_sum_id

            and t3.cont_pay_sum_detail_id = t4.cont_pay_sum_detail_id

            and t.enable_flg = 1

            andnvl(t.is_finance, 0) <> 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            and t3.biz_date between to_date('2016-01-01', 'yyyy-MM-dd') and

                to_date('2016-6-01', 'yyyy-MM-dd')

            and t4.entry_outbgitem_number = 'b.12'

          groupby t.project_cd) uu1

on uu1.project_cd = p.project_cd

leftjoin (select t.project_cd, sum(t4.convert_currency) as lastMonthDfk

           from cont_ledger         t,

                cont_actualpay      t1,

                cont_pay_sum        t2,

                cont_pay_sum_detail t3,

                cont_pay_detail     t4

          where t.cont_ledger_id = t1.cont_ledger_id

            and t1.cont_actualpay_id = t2.cont_actualpay_id

            and t2.cont_pay_sum_id = t3.cont_pay_sum_id

            and t3.cont_pay_sum_detail_id = t4.cont_pay_sum_detail_id

            and t.enable_flg = 1

            andnvl(t.is_finance, 0) <> 1

            andnvl(t.cont_type_cd2, 0) <> 9

            andnvl(t.tex_cont_flg, 0) <> 1

            and to_char(t3.biz_date, 'yyyy-mm') = '2016-05'

            and t4.entry_outbgitem_number = 'b.12'

          groupby t.project_cd) k1

on k1.project_cd = p.project_cd

where p.project_cd in (‘1248’)

3，查看表空间使用率
查看表空间使用率的sql语句：

select * from (

Select a.tablespace_name,

to_char(a.bytes/1024/1024,‘99,999.999’) total_bytes,

to_char(b.bytes/1024/1024,‘99,999.999’) free_bytes,

to_char(a.bytes/1024/1024 - b.bytes/1024/1024,‘99,999.999’) use_bytes,

to_char((1 - b.bytes/a.bytes)*100,‘99.99’) || '%'use

from (select tablespace_name,

sum(bytes) bytes

from dba_data_files

groupby tablespace_name) a,

(select tablespace_name,

sum(bytes) bytes

from dba_free_space

groupby tablespace_name) b

where a.tablespace_name = b.tablespace_name

unionall

select c.tablespace_name,

to_char(c.bytes/1024/1024,‘99,999.999’) total_bytes,

to_char( (c.bytes-d.bytes_used)/1024/1024,‘99,999.999’) free_bytes,

to_char(d.bytes_used/1024/1024,‘99,999.999’) use_bytes,

to_char(d.bytes_used*100/c.bytes,‘99.99’) || '%'use

from

(select tablespace_name,sum(bytes) bytes

from dba_temp_files groupby tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool groupby tablespace_name) d

where c.tablespace_name = d.tablespace_name

)

orderby tablespace_name

查看执行结果中TEMP的使用率已经到了99.58%了，报错的原因找到了，临时表空间被撑满了，如下图所示，所以需要扩容了：

4，查看普通数据文件是否扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d

查看临时表空间是否可以扩展：

select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;

看到，几个临时表空间的数据文件都不可以扩容，所以这也是报错的原因之一：

5，增加数据文件解决问题
问了同事，写这个sql语句的小伙伴已经离职半年了，所以无人懂这个复杂的sql的业务逻辑了，暂时优化sql的建议是无法去做了。采用另外一种发难，直接添加一个新的临时表空间的数据文件，设置大一些，设置成4g：

<-> 执行添加临时表空间的数据文件命令：

ALTERTABLESPACE TEMP

ADDTEMPFILE’/home/oradata/powerdes/temp05.dbf’

SIZE4G

AUTOEXTENDON

NEXT128M;

然后执行那条复杂的sql语句，就不会报错了，执行速度也很快，大概5秒左右执行完毕。

6，临时表空间相关
查看使用消耗临时表空间资源比较多的sql语句：

SELECT se.username,

     se.sid,

     su.extents,

     su.blocks * to_number(rtrim(p.value)) asSpace,

     tablespace,

     segtype,

     sql_text

FROM v$sor{t}_{u}sagesu,v$parameter p, v$sessionse,v$sql s

WHERE p.name = ‘db_block_size’

 AND su.session_addr = se.saddr

 AND s.hash_value = su.sqlhash

 AND s.address = su.sqladdr

ORDERBY se.username, se.sid;

增加数据文件

当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。

ALTERTABLESPACE TEMP

ADDTEMPFILE’/home/oradata/powerdes/temp05.dbf’

SIZE4G

AUTOEXTENDON

NEXT128M;

删除数据文件

例如，我想删除临时表空间下的某个文件，那么我们有两种方式删除临时表空间的数据文件。

方法1：

SQL> altertablespace temp droptempfile’/home/oradata/powerdes/temp03.dbf’ ;

Tablespace altered.

SQL>

这个方法会删除物理文件

[oracle@pldb1 ~]$ ll /home/oradata/powerdes/temp03.dbf

ls: cannot access /home/oradata/powerdes/temp03.dbf: No such file or directory

[oracle@pldb1 ~]$

方法2：

SQL> alterdatabasetempfile’/home/oradata/powerdes/temp04.dbf’dropincludingdatafiles;

Database altered.

SQL>

注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。

调整文件大小

如下例子，需要将临时数据文件从128M大小调整为256M

SQL> alterdatabasetempfile’/home/oradata/powerdes/temp02.dbf’resize256M;

Database altered.

SQL>

文件脱机联机

– 脱机

alterdatabasetempfile’/home/oradata/powerdes/temp02.dbf’offline;

– 联机

alterdatabasetempfile’/home/oradata/powerdes/temp02.dbf’online;

收缩临时表空间

排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。

SQL> ALTERTABLESPACE TEMP SHRINKSPACEKEEP8G;

SQL> ALTERTABLESPACE TEMP SHRINKTEMPFILE’/home/oradata/powerdes/temp05.dbf’