oracle调整undo大小,oracle undo表空间爆满处理

undo 表空间使用案例分析(UNDOTBS1使用率100%，enq：US-contention,释放undo)

转载 2013年02月20日 16:04:29 12007

问题描述：

近几天发现Oracle10.2.0.4数据库undo tablespace表空间使用率很高(最高时达到100%)，报警系统频繁报障undo tablespace表空间使用率过高。

TABLESPACE_NAME TOTAL USED FREE PUSED PFREE

UNDOTBS1 6141 % %

查询所有表空间使用情况(今天发现自己创建的表空间在使用完的情况，这个查询语句或者其它查询语句查不出已经使用完的表空间，只能自己删除掉一些使用该表空间的用户，以便释放自己创建的表空间)

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),'990.99') || '%' "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1;

问题分析

2.1 业务系统影响

undo tablespace表空间使用率达到100%时，没有业务系统用户反映系统出现表空间不能扩展的报错(ORA-30036)，但曾经出现过快照过旧报错(ORA-01555)。

2.2 数据库日志

查看alert_instdb.log报警文件，发现从Tue May 18 10:51:27 2010到Sat May 22 14:55:59 2010，共出现了15次与undo tablespace有关的ORA-01555报错，这些报错都是由同一条SQL语句引起的(SQL ID: 1h54gg6shbrkh)，语句执行时间都很长(在20万秒以上)，最长执行时间达到355659 sec。

Tue May 18 10:51:27 2010

ORA-01555 caused by SQL statement below (SQL ID: 1h54gg6shbrkh, Query Duration=349634 sec, SCN: 0x0a1b.a93246fa):

Tue May 18 10:51:27 2010

select decode(temp_kp.mc,null,temp_hdhs.mc,temp_kp.mc) as mc,

decode(temp_kp.jzyje,null, 0,temp_kp.jzyje) as jzyje,

decode(temp_kp.jzyfs,null, 0,temp_kp.jzyfs) as jzyfs,

decode(temp_kp.bdcje,null, 0,temp_kp.bdcje) as bdcje,

decode(temp_kp.bdcfs,null, 0,temp_kp.bdcfs) as bdcfs,

decode(temp_kp.ptje,null, 0,temp_kp.ptje) as ptje,

decode(temp_kp.ptfs,null, 0,temp_kp.ptfs) as ptfs,

decode(temp_kp.dkje,null, 0,temp_kp.dkje) as dkje,

decode(temp_kp.dkfs,null, 0,temp_kp.dkfs) as dkfs,

decode(temp_kp.bgdlyje,null, 0,temp_kp.bgdlyje) as bgdlyje,

decode(temp_kp.bgdlyfs,null, 0,temp_kp.bgdlyfs) as bgdlyfs,

decode(temp_kp.gjhyyje,null, 0,temp_kp.gjhyyje) as gjhyyje,

decode(temp_kp.gjhyyfs,null, 0,temp_kp.gjhyyfs) as gjhyyfs,

decode(temp_kp.gjhyycpdlje,null, 0