Oracle 临时表空间管理

DBA_TEMP_FILES：用于查看临时文件的大小。
V$TEMP_SPACE_HEADER：用于查看临时表空间的实际使用率

-- 正确的临时表空间使用率查询
SELECT
    D.TABLESPACE_NAME AS "表空间名",
    ROUND(D.BYTES / 1024 / 1024, 2) AS "总大小(MB)",
    ROUND((D.BYTES - NVL(F.BYTES_FREE, 0)) / 1024 / 1024, 2) AS "已使用(MB)",
    ROUND(NVL(F.BYTES_FREE, 0) / 1024 / 1024, 2) AS "空闲(MB)",
    ROUND((D.BYTES - NVL(F.BYTES_FREE, 0)) / D.BYTES * 100, 2) AS "使用率(%)"
FROM
    (SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES
     FROM DBA_TEMP_FILES
     GROUP BY TABLESPACE_NAME) D,
    (SELECT TABLESPACE_NAME, SUM(BYTES_FREE) AS BYTES_FREE
     FROM V$TEMP_SPACE_HEADER
     GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY "使用率(%)" DESC;

 

-- 查看临时表空间文件的当前大小、是否自动扩展及最大限制
SELECT FILE_NAME,
       BYTES/1024/1024 AS SIZE_MB,
       AUTOEXTENSIBLE,
       MAXBYTES/1024/1024 AS MAX_SIZE_MB
FROM DBA_TEMP_FILES;

 

-- 查询当前正在使用临时表空间的会话和SQL
SELECT se.username, se.sid, se.serial#, su.blocks, sq.sql_text
       FROM v$session se, v$sort_usage su, v$sql sq
WHERE se.saddr = su.session_addr AND su.sql_id = sq.sql_id;

 

-- 添加第二个临时文件
ALTER TABLESPACE TEMP 
ADD TEMPFILE '/同第一个路径/temp02.dbf' 
SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

-- 添加第三个...

 

--查看当前谁在用TEMP
SELECT s.username,
       s.sid,
       s.serial#,
       s.sql_id,
       ROUND(t.blocks * 8 / 1024 / 1024, 2) AS temp_gb,
       s.status,
       s.machine,
       s.program
FROM v$session s, v$tempseg_usage t
WHERE s.saddr = t.session_addr
ORDER BY t.blocks DESC;


--查看具体是哪个SQL在消耗TEMP
SELECT sql_id, sql_fulltext
FROM v$sql
WHERE sql_id IN (SELECT DISTINCT sql_id FROM v$tempseg_usage);


--如果占用100%并查不出是什么占用，直接收缩恢复正常
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 10G;