---临时表空间使用率
set line 200
set pages 999
col tablespace_name for A20
col table_name for A30
SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024, '99999999.999') "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes *100, 0), '990.00') "HWM % ",
TO_CHAR(NVL(t.bytes / 1024 / 1024, 0), '99999999.999') "Using (M)",
TO_CHAR(NVL(t.bytes / a.bytes*100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes
from gv$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
---查询临时表空间被谁占用
set line 200
set pages 999
col username for a10
col OSUSER for a10
col CLIENT_INFO for a10
col PROGRAM for a10
col tempseg_usage 9999
col inst_id a10
SELECT vt.inst_id,
vs.sid,
vs.serial#,
vs.username,
vs.osuser,
vs.machine,
vs.client_info,
vs.program,
vs.logon_time,
vt.tempseg_usage,
vt.segtype
FROM gv$session vs,
(SELECT inst_id,
username,
session_addr,
segtype,
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage
FROM gv$tempseg_usage
GROUP BY inst_id, username, session_addr, segtype
ORDER BY 4 DESC) vt
WHERE vs.inst_id = vt.inst_id
AND vs.saddr = vt.session_addr
order by tempseg_usage desc;
SELECT vt.inst_id,
vs.sid,
vs.serial#,
vs.username,
vs.osuser,
vs.machine,
-- vs.saddr,
vs.client_info,
vs.program,
-- vs.module,
vs.logon_time,
vt.tempseg_usage,
vt.segtype
FROM gv$session vs,
(SELECT inst_id,
username,
session_addr,
segtype,
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage
FROM gv$tempseg_usage
GROUP BY inst_id, username, session_addr, segtype
ORDER BY 4 DESC) vt
WHERE vs.inst_id = vt.inst_id
AND vs.saddr = vt.session_addr
order by tempseg_usage desc;