IT博客汇 | [原]ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段剖析解决

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

mchdba发表于 2016-06-15 20:45:09

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

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

1，同事说执行sql报错

2，查看报错sql语句

3，查看表空间使用率

4，查看普通数据文件是否扩展

5，增加数据文件解决问题

6，临时表空间相关

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