查看TEMP表空间使用率
--11G下:
select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent"
from dba_temp_free_space;
--10G下:
SELECT temp_used.tablespace_name,total - used as "Free",total as "Total",round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM
(SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes)/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;
查看单个SESSION消耗的TEMP表空间
SELECT S.sid,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used