一、现象
应用报错如下:
ORA-01652: 无法通过 1280 (在表空间 TEMP 中) 扩展 temp 段
该错误是因为TEMP临时表空间已占满,无法继续分配。
二、原因分析过程
1.怀疑pga太小,导致占用临时表空间
经查看,pga设置为1g,排除该原因
2.某服务调用频率异常
该服务有定时器刷新,也可以通过alt+r手动刷新服务,通过服务端日志发现,该服务调用频率异常,正常情况下每10秒调用一次,故障发生期间,出现每秒最多调用4次的情况。
进行模拟实验,手工高频率刷新服务(每秒调用服务20次),发现临时表空间并未被长时间占用,排除该原因。
3.外系统查询sql语句问题
我们将系统中的一个表的查询权限开放给了另外一个系统
select * from mytable t where rownum <= 1
如果外系统停了一段时间,mytable中就会积累很多数据(比如1万条数据),然后外系统恢复之后查询mytable(如果有数据就连续查询,如果没有数据,每2秒查询一次),每次只查一条数据,至少需要调用一万次,但是每次查询都无法使用索引。
经过和外系统同事沟通,并查看AWR报告,没有发现数据大量积累的情况,排除该原因。
4.lob字段问题
通过以下sql语句可以查看什么操作占用临时表空间,但是只有问题出现时,才能查询到数据。一次偶然的机会,查询到了数据。
SELECT TMP_TBS.TABLESPACE_NAME,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') PARA
GROUP BY TMP_USED.TABLESPACE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;
----------------------------------------------
TABLESPACE_NAME TOTAL_MB USED_MB USED_PERSENT
TEMP 100 10 10
上面的结果中(测试环境模拟),已占用的临时表空间为10MB,而且长时间不释放,通过以下语句可以看到详细的占用情况
select * from v$sort_usage
-----------------------------------------------
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
USER USER 07000000473F73C8 14448 070000002C444940 1438138001 2b9476javhgnj TEMP TEMPORARY LOB_DATA 202 6409 1 1280 1
其中的BLOCKS为占用的数据块数,单位为8KB,1280*8KB=10MB,和第一条查询语句的结果相符。其中SQLADDR表示具体sql语句的地址。通过以下语句可以查看具体sql语句。
select sql_text ,address from v$sql s where address='070000002C444940';
-----------------------------------------------
SQL_TEXT ADDRESS
insert into mytable(AHM_FLTNO, AHM_DATE, AHM_TYPE_AD, AHM_TYPE_ID, AHM_MSG_TYPE, AHM_MSG_SUBTYPE, AHM_ORIG_AIRPORT, AHM_DEST_AIRPORT, AHM_CONTENT, AHM_CONTENT_EXT, AHM_CONTENT_DISPLAY, AHM_SENDER_MACHINE, AHM_SENDER, AHM_SENDER_GRP, AHM_RCVER_MACHINE, AHM_RCVER_GRP, AHM_RCVER, AHM_STATE, AHM_DATATYPE, AHM_PRIORITY, AHM_TIME, AHM_OPTM, AHM_EXPIRE, AHM_VERSION, AHM_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25) 070000002C444940
确定sql语句后,通过查询应用中的代码发现,mytable表的插入操作使用hibernate,其中AHM_CONTENT_EXT字段使用了clob类型,在hibernate中操作clob类型,需要使用自定义类型,将String转换成clob。
在该自定义类型中,使用以下语句创建了临时clob对象,但是没有释放。
CLOB tempClob = CLOB.createTemporary(ConnectionHelp.getConnection(conn), true, CLOB.DURATION_SESSION);
三、解决方法
应用使用hibernate版本为3.1.3,在hibernate新版本中已经实现了该类,使用Hibernate-3.3.2.GA实现的StringClobType.java
http://www.docjar.com/html/api/org/hibernate/type/StringClobType.java.html
四、分析过程中的疑问
1.刚开始发现问题时,我们通过以下语句进行查询
SELECT se.username,
se.sid,
se.serial#,
se.sql_address,
se.machine,
se.program,
su.sqladdr,
su.tablespace,
su.segtype,
su.contents
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;
发现查询到的sql_address对应的sql为
select 1 from dual
正常情况,以上语句不会占用临时表空间,为什么查询出来的是这个语句。
经过分析,我们可以发现,这个语句是TongWeb用来定时探测数据库状态的语句。上面的语句是基于session作为关联条件的,也就是说,在出现问题的那个时间点,用上面的语句查询,是可以查询到真实的sql语句,但是在tongweb对数据库探测后,该session的当前sql语句已经变成了select 1 from dual。
v$session有另外一个字段prev_sql_addr,表示上一条sql语句的地址,但也不能保证一定是问题语句对应的地址
2.有些数据库(测试中使用的oracle 9i)一个消息占用1MB,有些数据库(测试中使用oracle 10g)一个消息占用10MB
该问题与临时表空间INITIAL_EXTENT设置有关,使用以下语句可以查看具体设置:
select BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name='TEMP';
每条消息占用10MB左右(oracle 10g)
---------------------------------------
BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT ALLOCATION_TYPE
8192 10485760 10485760 LOCAL UNIFORM
每条消息占用1MB左右(oracle 9i)
---------------------------------------
BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT ALLOCATION_TYPE
8192 1048576 1048576 LOCAL UNIFORM
3.占用临时表空间到80MB就不再上升
测试时临时表空间设置为100MB,发送消息后,临时表空间被占用逐渐增多,但是占用到80MB就不再上升了。
该现象与应用服务器(WAS或TongWeb)数据库连接池设置有关,以测试时使用的TongWeb为例,配置如下:
最小连接数:8
空闲超时:300秒
也就是说,在并发量较小,连接数小于等于8时,最多占用80MB临时表空间。
经过测试,修改“最小连接数”为15后,占用临时表空间情况会持续上升,突破80MB。
4.在应用中调用freeTemporary后,clob占用的临时表空间不释放
经查是oracle的bug,5723140。
在10.2.0.4中需要使用以下语句解决该问题
alter session set events '60025 trace name context forever';
经过测试,以上修改对存储过程中使用lob对象有效,但是不清楚在java中怎么进行相应的设置。
5.有时一条记录占用30MB
多数只占用10MB,偶尔占用30MB,没有重现(数据量都不大,不应该超过10MB)。
五、参考文章
1.Oracle常用性能监控语句解析
http://www.cnblogs.com/preftest/archive/2010/11/14/1876856.html
2.查看oracle 系统临时表空间、undo表空间、SGA和PGA大小
http://blog.csdn.net/xueyepiaoling/article/details/6187842
3.临时表空间使用情况
http://space.itpub.net/?uid-13605188-action-viewspace-itemid-678109
4.Temporary LOB导致临时表空间暴满
http://pingshx.itpub.net/post/39434/484280
5.Oracle Database List of Bugs Fixed 10g Release 2 (10.2.0.4) Patch Set 3
http://www.eygle.com/Notes/10204_buglist.htm
六、其他相关sql语句
1.查看临时表空间数据文件
Select file#,status,name from v$tempfile
2.修改临时表空间大小
alter database tempfile '/app/oradata2/oraapp/newapp_temp_01.dbf' resize 150M
3.修改表空间大小
alter database datafile 'xx.dbf' resize xxxx
分享到:
相关推荐
Oracle释放临时表空间脚本
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。 重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会...
表空间概述 Oracle的表空间属于Oracle中的存储结构,是一种用于存储数据库...临时表空间:存储数据库的中间执行过程,如:保存order by数据库排序,分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空
truncate table 表名 (删除表中记录时释放表空间) DML 语句: 表级共享锁: 对于操作一张表中的不同记录时,互不影响 行级排它锁:对于一行记录,oracle 会只允许只有一个用户对它在同一时间进行修改操作 wait() ...
什么是表空间? Oracle数据库包含逻辑结构和物理文件。数据库的物理结构是指构成数据库的一组操作系统文件数据库的逻辑结构是指...临时表空间是通用的,所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间
8-14 缺省临时表空间 8-15 缺省临时表空间的限制 8-16 脱机状态 8-17 只读表空间 8-19 删除表空间 8-20 改变表空间的大小 8-21 允许数据文件的自动增长 8-22 手工改变数据文件的大小 8-23 给表空间添加数据文件 8-24...
7.8.7 恢复丢失的临时表空间 7.8.8 只读表空间丢失 7.8.9 索引表空间丢失 7.9 恢复联机重做日志 7.9.1 联机重做日志组某个成员丢失 7.9.2 非活动的重做日志组丢失 7.9.3 活动重做日志组丢失 7.10 恢复丢失的...
Oracle server 可以有条理的通过表空间以及段、扩展、数据块控制磁盘空间,表空间(Tablespaces):Oracle database 的数据存储在表空间中。 逻辑结构的层次如下所述: Oracle 数据库至少包含一个表空间。表空间包含...
3.2.7.5 临时表空间组及其妙用114 3.3 课程结束你给程序安上了翅膀 117 3.3.1 过度扩展与性能 117 3.3.2 PCTFREE与性能120 3.3.3 行迁移与优化 123 3.3.4 块的大小与应用 124 第4章祝贺,表的设计成就英雄 131 4.1 ...
2.Delete无法释放空间(HWM High Water Mark不下降) 3.表记录太大检索太慢 4.索引回表读开销很大 5.即便有序插入,也很难保证有序读出 二、全局临时表:适合接口表设计 优点: 1.高效删除 2.产生很少的日志 3.不同的...
§3.3.6 将表移动到新的数据段或新的表空间 102 §3.3.7 手工分配表的存储空间 104 §3.3.8 标记不使用的列和删除不使用的列 104 §3.3 主键 106 §3.3.1 创建主键 106 §3.3.2 改变主键 109 §3.3.3 删除主键 109 ...
它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来将一个索引转移到其他表空间。 八、其他 1、truncate 分区...
§9.4.4 临时表空间设计规划 100 §9.4.5 数据文件和日志文件在不同磁盘上 101 §9.5 数据库物理设计 101 §9.5.1 定量估计 101 §9.5.2 表空间与数据文件 102 §9.5.3 物理设计原则 103 §9.5.4 数据库物理设计内容...
组成:表空间、段、区、块的组成层次 六、 oracle安装、卸载和启动 硬件要求 物理内存:1GB 可用物理内存:50M 交换空间大小:3.25GB 硬盘空间:10GB 安装 1. 安装程序成功下载,将会得到如下2个文件: ...
8.8 Oracle 11g UNDO表空间备份增强 360 8.9 回滚机制的深入研究 361 8.9.1 从DML更新事务开始 361 8.9.2 获得事务信息 362 8.9.3 获得回滚段名称并转储段头信息 362 8.9.4 获得跟踪文件信息 363 8.9.5...
数据库表空间分为系统表空间和非系统表空间两类,其中非系统表空间包括撤销表空间、临时表空间和用户表空间等。 SYSTEM表空间主要用于存储数据库的数据字典、PL/SQL程序的源代码和解释代码、数据库对象的定义。撤销...
首先要停止数据库, 将这个表所在的表空间的文件拷贝出来, 因为Oracle在Truncate只时将相应Segment的第一个块格式化掉了, 而后面的都还存在, 到下次用时到才真正地重新格式化. 下面来讲一个Truncate表后进行恢复的...
Oracle9i初始化参数中文说明 Blank_trimming: 说明: 如果值为TRUE, 即使源长度比目标长度 (SQL92 兼容) 更长, 也允许分配数据。 值范围: TRUE | FALSE 默认值: FALSE serializable: 说明: 确定查询是否获取表级...
注意,如果是选择了专门建立toad这个用户的话,需要先修改一下脚本,指定用户的默认表空间和临时表空间。 需要使用Oracle8i 的Profile analyzer,必须运行ToadProfiler.sql 需要加强Toad的安全性,必须...