项目报错:nested exception is java.sql.sqlexception: ora-01652: unable to extend temp segment by 128 in tablespace temp
原因是临时表空间满了,临时表空间一直增长,未释放导致临时表空间使用率100%。
查询临时表空间使用率
--临时表空间利用率 select c.tablespace_name "临时表空间名", round(c.bytes / 1024 / 1024 / 1024, 2) "临时表空间大小(g)", round((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, 2) "临时表空间剩余大小(g)", round(d.bytes_used / 1024 / 1024 / 1024, 2) "临时表空间使用大小(g)", round(d.bytes_used * 100 / c.bytes, 4) || '%' "使用率 %" from (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name;
解决办法一:
--压缩一下临时表空间 --自动将表空间的临时文件缩小到最小可能的大小 alter tablespace temp shrink space;
解决办法二:
查询临时表空间位置,创建新的临时表空间
-- 查询临时表空间位置 select file_id, tablespace_name "临时表空间名", bytes / 1024 / 1024 / 1024 "表空间大小(g)", file_name "文件路径" from dba_temp_files order by tablespace_name, file_name; -- 例如查询结果如下: -- /dev/shm/oradata/temp01.dbf -- 创建新的临时表空间最好也放在这个目录下 -- 创建临时表空间 create temporary tablespace irfs_temp tempfile '/dev/shm/oradata/irfs_temp01.dbf' size 20g autoextend off;
切换临时表空间为新的临时表空间,切换后删除原来的临时表空间。
-- 设置数据库的默认临时表空间,切换临时表空间 alter database default temporary tablespace irfs_temp; --查询默认的临时表空间 select property_name, property_value from database_properties where property_name='default_temp_tablespace'; -- 删除原来的临时表空间(包括文件) drop tablespace temp including contents and datafiles;
解决办法三:
前两种方案,需要每隔一段时间就要去手动操作一次。
哪些情况会占用临时表空间?
1、当数据库执行如create index、order by、group by等操作时,如果内存中的排序区域大小不足,就会将数据放入临时表空间中进行排序。
2、操作clob或blob字段时,如果内存中的空间不足以容纳这些数据,oracle会将这些数据放入临时表空间。
查询临时表空间占用sql
--查询临时表空间占用sql select se.username, se.sid, se.serial#, se.sql_id, se.sql_address, se.machine, sa.sql_text, sa.sql_fulltext, se.program, su.tablespace, su.segtype, su.contents from v$session se, v$sort_usage su, v$sqlarea sa where se.saddr=su.session_addr and se.sql_id=sa.sql_id
表空间的释放通常依赖于事务提交或会话的断开。
事务的提交释放了事务占用的资源,包括临时表空间中的空间。
会话的断开也会释放该会话使用的表空间。
因此,如果临时表空间没有被释放,并不是由于自动扩展设置的原因。
在查找表空间未释放的原因时,您应该关注未提交的事务或仍然处于活动状态的会话。
对于临时表空间的释放问题,您可以继续检查未提交的事务或会话,并确保它们被正确提交或断开连接。
我的oracle数据库版本是11gr2(11.2.0.4)
我这里是由于clob或者blob字段造成的。
具体原因是clob或者blob字段使用后会占用临时表空间,如果连接不断开就不会释放,只要想办法让连接使用后断开就行。
我使用了druid连接池,由于我的业务一天24小时都会使用,所以连接池中的连接一直处于活跃状态,没有到达配置的空闲5分钟删除掉连接,
当然也可以从空闲时间参数入手让空闲时间短点就删除掉连接,一句话就是想办法让连接断开,但是频繁的创建连接也不好那连接池也没有意义了。
解决思路,不要使用clob或者blob字段,想办法使用其它方案替代,我这里必须要用到clob,又没有找到替代方案。
我后面解决思路是,写了一个定时器,10分钟检测一次连接池,连接存活时间超过1天,就删除该连接,且一次最多删除一个连接防止把连接池清空了。该方案自行评估有无风险!
package com.study.pool; import com.alibaba.druid.pool.druiddatasource; import com.alibaba.druid.pool.druidpooledconnection; import lombok.extern.slf4j.slf4j; import org.springframework.beans.factory.annotation.autowired; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.jdbc.datasource.datasourceutils; import org.springframework.scheduling.annotation.enablescheduling; import org.springframework.scheduling.annotation.scheduled; import org.springframework.stereotype.component; import javax.sql.datasource; import java.sql.connection; /** * 清理连接存活时间超过1天的连接 * 由于clob和blob字段导致临时表空间不释放,需要定期清理连接 * @date: 2024/2/29 16:49 */ @slf4j @component @enablescheduling public class druidpooledclear { @autowired private jdbctemplate jdbctemplate; // @postconstruct @scheduled(cron = "25 1/10 * * * ?") //10分钟一次 public void clearconnection() { try { datasource datasource = jdbctemplate.getdatasource(); if (datasource instanceof druiddatasource) { druiddatasource druiddatasource = (druiddatasource) datasource; clearconnection(datasource, druiddatasource); } } catch (exception e) { log.error(e.getmessage(), e); } } /** * 清理连接,1次只清理一个连接,防止一次性把连接池清空 * @date 2024/2/29 16:59 */ private void clearconnection(datasource datasource, druiddatasource druiddatasource) { druidpooledconnection druidpooledconnection = null; try { // 由于druiddatasource.getconnection()总是获取上一次使用的连接(最后一次使用的连接),无法遍历空闲连接,只有使用递归才获取所有空闲连接 druidpooledconnection = druiddatasource.getconnection(); // log.info("连接:" druidpooledconnection.getconnectionholder()); // 连接创建单位:毫秒 long connectedtimemillis = druidpooledconnection.getconnectionholder().getconnecttimemillis(); // 删除连接,连接存活时间超过1天 if (system.currenttimemillis() > connectedtimemillis 1000 * 60 * 60 * 24) { log.info("删除连接:" druidpooledconnection.getconnectionholder()); // 这一步很关键,druidpooledconnection.getconnection() 取出的连接,已经不能归还给连接池了 connection connection = druidpooledconnection.getconnection(); // 从连接池中移除连接 datasourceutils.releaseconnection(connection, datasource); } else { // int activecount = druiddatasource.getactivecount();//活跃连接数 int poolingcount = druiddatasource.getpoolingcount();//空闲连接数 // log.info("池中连接数:{},活跃连接数:{},空闲连接数:{}", activecount poolingcount, activecount, poolingcount); if (poolingcount > 0) { clearconnection(datasource, druiddatasource); } } } catch (exception e) { log.error(e.getmessage(), e); } finally { // 归还连接给连接池 datasourceutils.releaseconnection(druidpooledconnection, datasource); } } }
以上就是oracle临时表空间无法释放的解决办法的详细内容,更多关于oracle临时表空间无法释放的资料请关注其它相关文章!