oracle臨時表空間無法釋放的解決辦法
項目報錯:nested exception is java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
原因是臨時表空間滿了,臨時表空間一直增長,未釋放導(dǎo)致臨時表空間使用率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è)置數(shù)據(jù)庫的默認(rèn)臨時表空間,切換臨時表空間 alter database default temporary tablespace IRFS_TEMP; --查詢默認(rèn)的臨時表空間 SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; -- 刪除原來的臨時表空間(包括文件) drop tablespace TEMP including contents and datafiles;
解決辦法三:
前兩種方案,需要每隔一段時間就要去手動操作一次。
哪些情況會占用臨時表空間?
1、當(dāng)數(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
表空間的釋放通常依賴于事務(wù)提交或會話的斷開。
事務(wù)的提交釋放了事務(wù)占用的資源,包括臨時表空間中的空間。
會話的斷開也會釋放該會話使用的表空間。
因此,如果臨時表空間沒有被釋放,并不是由于自動擴展設(shè)置的原因。
在查找表空間未釋放的原因時,您應(yīng)該關(guān)注未提交的事務(wù)或仍然處于活動狀態(tài)的會話。
對于臨時表空間的釋放問題,您可以繼續(xù)檢查未提交的事務(wù)或會話,并確保它們被正確提交或斷開連接。
我的Oracle數(shù)據(jù)庫版本是11gR2(11.2.0.4)
我這里是由于clob或者blob字段造成的。
具體原因是clob或者blob字段使用后會占用臨時表空間,如果連接不斷開就不會釋放,只要想辦法讓連接使用后斷開就行。
我使用了druid連接池,由于我的業(yè)務(wù)一天24小時都會使用,所以連接池中的連接一直處于活躍狀態(tài),沒有到達(dá)配置的空閑5分鐘刪除掉連接,
當(dāng)然也可以從空閑時間參數(shù)入手讓空閑時間短點就刪除掉連接,一句話就是想辦法讓連接斷開,但是頻繁的創(chuàng)建連接也不好那連接池也沒有意義了。
解決思路,不要使用clob或者blob字段,想辦法使用其它方案替代,我這里必須要用到clob,又沒有找到替代方案。
我后面解決思路是,寫了一個定時器,10分鐘檢測一次連接池,連接存活時間超過1天,就刪除該連接,且一次最多刪除一個連接防止把連接池清空了。該方案自行評估有無風(fēng)險!
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字段導(dǎo)致臨時表空間不釋放,需要定期清理連接 * @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臨時表空間無法釋放的解決辦法的詳細(xì)內(nèi)容,更多關(guān)于oracle臨時表空間無法釋放的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
解決Oracle刪除重復(fù)數(shù)據(jù)只留一條的方法詳解
本篇文章是對Oracle刪除重復(fù)數(shù)據(jù)只留一條的解決方法進行了詳細(xì)的分析介紹,需要的朋友參考下2013-05-05Oracle數(shù)據(jù)庫表名支持的最大長度是多少
這篇文章主要介紹了Oracle數(shù)據(jù)庫表名支持的最大長度,本文通過Oracle標(biāo)識符確認(rèn)了表名的最大支持字符串為30個字符,需要的朋友可以參考下2014-08-08Oracle出現(xiàn)超出打開游標(biāo)最大數(shù)的解決方法
這篇文章主要介紹了Oracle出現(xiàn)超出打開游標(biāo)最大數(shù)的解決方法,涉及針對Oracle游標(biāo)位置的判斷與處理技巧,具有一定參考借鑒價值,需要的朋友可以參考下2016-06-06oracle的導(dǎo)入導(dǎo)出注意事項及心得分享
導(dǎo)入導(dǎo)出oracle方案是備份和恢復(fù)的主旋律,有一點點在使用過程中的體會,在此與大家分享下,希望對大家有所幫助2013-09-09Oracle CBO優(yōu)化模式中的5種索引訪問方法淺析
這篇文章主要介紹了Oracle CBO優(yōu)化模式中的5種索引訪問方法淺析,包含索引唯一掃描、索引范圍掃描、索引全掃描、索引跳躍掃描、索引快速全掃描等內(nèi)容,需要的朋友可以參考下2014-07-07