--臨時表空間利用率
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;
 
 
-- 例如查詢結(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;

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

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);
        }
    }
}