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,

S.serial# sid_serial,

s.INST_ID,

S.username,

S.osuser,

P.spid,

S.module,

P.program,

T.tablespace,

q.sql_text,

nvl(S.sql_id,S.PREV_SQL_ID) sql_id

--COUNT (*) statements

FROM gv$sort_usage T,

gv$session S,

dba_tablespaces TBS,

gv$process P,

gv$sql    q

WHERE     T.session_addr = S.saddr

AND t.INST_ID = S.INST_ID

AND S.paddr = P.addr

AND S.INST_ID = P.INST_ID

AND nvl(S.sql_id,S.PREV_SQL_ID)=q.sql_id

AND T.tablespace = TBS.tablespace_name

and s.sid='111'

GROUP BY S.sid,

S.serial#,

s.INST_ID,

S.username,

S.osuser,

P.spid,

S.module,

P.program,

TBS.block_size,

T.tablespace,

q.sql_text,

nvl(S.sql_id,S.PREV_SQL_ID)

order by 2 desc

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1456043/，如需转载，请注明出处，否则将追究法律责任。