查询临时表空间使用率
--临时表空间利用率
selectc.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(selecttablespace_name,sum(bytes)bytes
fromdba_temp_files
GROUPbytablespace_name)c,
(selecttablespace_name,sum(bytes_cached)bytes_used
fromv$temp_extent_pool
GROUPbytablespace_name)d
wherec.tablespace_name=d.tablespace_name;
解决办法一:
--压缩一下临时表空间
--自动将表空间的临时文件缩小到最小可能的大小
altertablespaceTEMPshrinkspace;
解决办法二:
查询临时表空间位置,创建新的临时表空间
--查询临时表空间位置
SELECTFILE_ID,
TABLESPACE_NAME"临时表空间名",
BYTES/1024/1024/1024"表空间大小(G)",
FILE_NAME"文件路径"
FROMDBA_TEMP_FILES
orderbyTABLESPACE_NAME,FILE_NAME;
--例如查询结果如下:
--/dev/shm/oradata/temp01.dbf
--创建新的临时表空间最好也放在这个目录下
--创建临时表空间
createtemporarytablespaceIRFS_TEMP
tempfile'/dev/shm/oradata/irfs_temp01.dbf'
size20g
autoextendoff;
切换临时表空间为新的临时表空间,切换后删除原来的临时表空间。
--设置数据库的默认临时表空间,切换临时表空间
alterdatabasedefaulttemporarytablespaceIRFS_TEMP;
--查询默认的临时表空间
SELECTPROPERTY_NAME,PROPERTY_VALUE
FROMDATABASE_PROPERTIES
WHEREPROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
--删除原来的临时表空间(包括文件)
droptablespaceTEMPincludingcontentsanddatafiles;
解决办法三:
前两种方案,需要每隔一段时间就要去手动操作一次。
哪些情况会占用临时表空间?
1、当数据库执行如CREATEINDEX、ORDERBY、GROUPBY等操作时,如果内存中的排序区域大小不足,就会将数据放入临时表空间中进行排序。
2、操作CLOB或BLOB字段时,如果内存中的空间不足以容纳这些数据,Oracle会将这些数据放入临时表空间。
查询临时表空间占用sql
--查询临时表空间占用sql
SELECTse.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
FROMv$sessionse,
v$sort_usagesu,
v$sqlareasa
WHEREse.saddr=su.session_addrandse.SQL_ID=sa.SQL_ID
表空间的释放通常依赖于事务提交或会话的断开。
事务的提交释放了事务占用的资源,包括临时表空间中的空间。
会话的断开也会释放该会话使用的表空间。
因此,如果临时表空间没有被释放,并不是由于自动扩展设置的原因。
在查找表空间未释放的原因时,您应该关注未提交的事务或仍然处于活动状态的会话。
对于临时表空间的释放问题,您可以继续检查未提交的事务或会话,并确保它们被正确提交或断开连接。
我的Oracle数据库版本是11gR2(11.2.0.4)
我这里是由于clob或者blob字段造成的。
具体原因是clob或者blob字段使用后会占用临时表空间,如果连接不断开就不会释放,只要想办法让连接使用后断开就行。
我使用了druid连接池,由于我的业务一天24小时都会使用,所以连接池中的连接一直处于活跃状态,没有到达配置的空闲5分钟删除掉连接,
当然也可以从空闲时间参数入手让空闲时间短点就删除掉连接,一句话就是想办法让连接断开,但是频繁的创建连接也不好那连接池也没有意义了。
解决思路,不要使用clob或者blob字段,想办法使用其它方案替代,我这里必须要用到clob,又没有找到替代方案。
我后面解决思路是,写了一个定时器,10分钟检测一次连接池,连接存活时间超过1天,就删除该连接,且一次最多删除一个连接防止把连接池清空了。该方案自行评估有无风险!
packagecom.study.pool;
importcom.alibaba.druid.pool.DruidDataSource;
importcom.alibaba.druid.pool.DruidPooledConnection;
importlombok.extern.slf4j.Slf4j;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.jdbc.core.JdbcTemplate;
importorg.springframework.jdbc.datasource.DataSourceUtils;
importorg.springframework.scheduling.annotation.EnableScheduling;
importorg.springframework.scheduling.annotation.Scheduled;
importorg.springframework.stereotype.Component;
importjavax.sql.DataSource;
importjava.sql.Connection;
/**
*清理连接存活时间超过1天的连接
*由于clob和blob字段导致临时表空间不释放,需要定期清理连接
*@Date:2024/2/2916:49
*/
@Slf4j
@Component
@EnableScheduling
publicclassDruidPooledClear{
@Autowired
privateJdbcTemplatejdbcTemplate;
//@PostConstruct
@Scheduled(cron="251/10***?")//10分钟一次
publicvoidclearConnection(){
try{
DataSourcedataSource=jdbcTemplate.getDataSource();
if(dataSourceinstanceofDruidDataSource){
DruidDataSourcedruidDataSource=(DruidDataSource)dataSource;
clearConnection(dataSource,druidDataSource);
}
}catch(Exceptione){
log.error(e.getMessage(),e);
}
}
/**
*清理连接,1次只清理一个连接,防止一次性把连接池清空
*@date2024/2/2916:59
*/
privatevoidclearConnection(DataSourcedataSource,DruidDataSourcedruidDataSource){
DruidPooledConnectiondruidPooledConnection=null;
try{
//由于druidDataSource.getConnection()总是获取上一次使用的连接(最后一次使用的连接),无法遍历空闲连接,只有使用递归才获取所有空闲连接
druidPooledConnection=druidDataSource.getConnection();
//log.info("连接:"+druidPooledConnection.getConnectionHolder());
//连接创建单位:毫秒
longconnectedTimeMillis=druidPooledConnection.getConnectionHolder().getConnectTimeMillis();
//删除连接,连接存活时间超过1天
if(System.currentTimeMillis()>connectedTimeMillis+1000*60*60*24){
log.info("删除连接:"+druidPooledConnection.getConnectionHolder());
//这一步很关键,druidPooledConnection.getConnection()取出的连接,已经不能归还给连接池了
Connectionconnection=druidPooledConnection.getConnection();
//从连接池中移除连接
DataSourceUtils.releaseConnection(connection,dataSource);
}else{
//intactiveCount=druidDataSource.getActiveCount();//活跃连接数
intpoolingCount=druidDataSource.getPoolingCount();//空闲连接数
//log.info("池中连接数:{},活跃连接数:{},空闲连接数:{}",activeCount+poolingCount,activeCount,poolingCount);
if(poolingCount>0){
clearConnection(dataSource,druidDataSource);
}
}
}catch(Exceptione){
log.error(e.getMessage(),e);
}finally{
//归还连接给连接池
DataSourceUtils.releaseConnection(druidPooledConnection,dataSource);
}
}
}
|