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;

-- 添加第三个...