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$sort_usage su, v$parameter p, v$session se, 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'

 

 

参考文档：

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#i1013552