oracle临时表空间无法释放的解决办法_Oracle

项目报错：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临时表空间无法释放的资料请关注代码网其它相关文章！