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 s o r t u s a g e s u , v sort_usage su, v sortusagesu,vparameter p, v s e s s i o n s e , v session se, v sessionse,vsql 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’