查看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
--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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26084062/viewspace-1456043/