oracle查看空间temp,查看单个SQL消耗TEMP表空间以及TEMP表空间使用率

查看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