柚子快報邀請碼778899分享：oracle臨時表空間不釋放

http://yzkb.51969.com/

項目報錯 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;

解決辦法二：

查詢臨時表空間位置，創(chuàng)建新的臨時表空間

-- 查詢臨時表空間位置

SELECT FILE_ID,

TABLESPACE_NAME "臨時表空間名",

BYTES / 1024 / 1024 / 1024 "表空間大小(G)",

FILE_NAME "文件路徑"

FROM DBA_TEMP_FILES

order by TABLESPACE_NAME, FILE_NAME;

-- 例如查詢結(jié)果如下：

-- /dev/shm/oradata/temp01.dbf

-- 創(chuàng)建新的臨時表空間最好也放在這個目錄下

-- 創(chuàng)建臨時表空間

create temporary tablespace IRFS_TEMP

tempfile '/dev/shm/oradata/irfs_temp01.dbf'

size 20g

autoextend off;

切換臨時表空間為新的臨時表空間，切換后刪除原來的臨時表空間。

-- 設置數(shù)據(jù)庫的默認臨時表空間,切換臨時表空間

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、當數(shù)據(jù)庫執(zhí)行如CREATE INDEX、ORDER BY、GROUP BY等操作時，如果內(nèi)存中的排序區(qū)域大小不足，就會將數(shù)據(jù)放入臨時表空間中進行排序。 2、操作CLOB或BLOB字段時，如果內(nèi)存中的空間不足以容納這些數(shù)據(jù)，Oracle會將這些數(shù)據(jù)放入臨時表空間。

查詢臨時表空間占用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

表空間的釋放通常依賴于事務提交或會話的斷開。 事務的提交釋放了事務占用的資源，包括臨時表空間中的空間。 會話的斷開也會釋放該會話使用的表空間。

因此，如果臨時表空間沒有被釋放，并不是由于自動擴展設置的原因。 在查找表空間未釋放的原因時，您應該關(guān)注未提交的事務或仍然處于活動狀態(tài)的會話。 對于臨時表空間的釋放問題，您可以繼續(xù)檢查未提交的事務或會話，并確保它們被正確提交或斷開連接。

我的Oracle數(shù)據(jù)庫版本是11gR2（11.2.0.4）

我這里是由于clob或者blob字段造成的。 具體原因是clob或者blob字段使用后會占用臨時表空間，如果連接不斷開就不會釋放，只要想辦法讓連接使用后斷開就行。 我使用了druid連接池，由于我的業(yè)務一天24小時都會使用，所以連接池中的連接一直處于活躍狀態(tài)，沒有到達配置的空閑5分鐘刪除掉連接， 當然也可以從空閑時間參數(shù)入手讓空閑時間短點就刪除掉連接，一句話就是想辦法讓連接斷開，但是頻繁的創(chuàng)建連接也不好那連接池也沒有意義了。

解決思路，不要使用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());

// 連接創(chuàng)建單位：毫秒

long connectedTimeMillis = druidPooledConnection.getConnectionHolder().getConnectTimeMillis();

// 刪除連接，連接存活時間超過1天

if (System.currentTimeMillis() > connectedTimeMillis + 1000 * 60 * 60 * 24) {

log.info("刪除連接：" + druidPooledConnection.getConnectionHolder());

// 這一步很關(guān)鍵，druidPooledConnection.getConnection() 取出的連接，已經(jīng)不能歸還給連接池了

Connection connection = druidPooledConnection.getConnection();

// 從連接池中移除連接

DataSourceUtils.releaseConnection(connection, dataSource);

} else {

// int activeCount = druidDataSource.getActiveCount();//活躍連接數(shù)

int poolingCount = druidDataSource.getPoolingCount();//空閑連接數(shù)

// log.info("池中連接數(shù)：{}，活躍連接數(shù)：{}，空閑連接數(shù)：{}", activeCount + poolingCount, activeCount, poolingCount);

if (poolingCount > 0) {

clearConnection(dataSource, druidDataSource);

}

}

} catch (Exception e) {

log.error(e.getMessage(), e);

} finally {

// 歸還連接給連接池

DataSourceUtils.releaseConnection(druidPooledConnection, dataSource);

}

}

}

參考：

oracle的臨時表空間無法釋放！多半是blob害的。 - 墨天輪

柚子快報邀請碼778899分享：oracle臨時表空間不釋放

http://yzkb.51969.com/

參考鏈接