【Oracle SQL】临时表空间管理

---

一、查询临时表空间使用率

SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
       TT.TOTAL - TU.USED                                    AS "FREE(G)",
       TT.TOTAL                                              AS "TOTAL(G)",
       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME, 
              allocated_space / 1024 / 1024 / 1024 USED
       FROM dba_temp_free_space) TU ,
     (SELECT TABLESPACE_NAME,
              SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
       FROM DBA_TEMP_FILES
       GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

二、查询临时表空间占用

1.查询真正消耗高temp表空间的sql语句

--查询真正消耗高temp表空间的sql语句
SELECT distinct a.*, sq.sql_id, sq.sql_text
  FROM (SELECT su.tablespace,
               s.sid,
               s.serial#,
               s.username,
               s.osuser,
               s.status,
               s.module,
               s.program,
               s.machine,
               sum(su.blocks) * tbs.block_size / 1024 / 1024 used_mb,
               su.segtype,