临时表空间使用率和被谁占用情况

---临时表空间使用率
set line 200\r
set pages 999\r
col tablespace_name for A20\r
col table_name for A30\r
SELECT d.tablespace_name "Name",\r
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",\r
TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024, '99999999.999') "HWM (M)",\r
TO_CHAR(NVL(t.hwm / a.bytes *100, 0), '990.00') "HWM % ",\r
TO_CHAR(NVL(t.bytes / 1024 / 1024, 0), '99999999.999') "Using (M)",\r
TO_CHAR(NVL(t.bytes / a.bytes*100, 0), '990.00') "Using %" \r
FROM sys.dba_tablespaces d,\r
(select tablespace_name, sum(bytes) bytes \r
from dba_temp_files \r
group by tablespace_name) a,\r
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes \r
from gv$temp_extent_pool \r
group by tablespace_name) t \r
WHERE d.tablespace_name = a.tablespace_name(+) \r
AND d.tablespace_name = t.tablespace_name(+) \r
AND d.extent_management like 'LOCAL' \r
AND d.contents like 'TEMPORARY';

---查询临时表空间被谁占用
set line 200\r
set pages 999\r
col username for a10 \r
col OSUSER for a10 \r
col CLIENT_INFO for a10 \r
col PROGRAM for a10 \r
col tempseg_usage 9999 \r
col inst_id a10 \r
SELECT vt.inst_id,\r
vs.sid,\r
vs.serial#,\r
vs.username,\r
vs.osuser,\r
vs.machine,\r
vs.client_info,\r
vs.program,\r
vs.logon_time,\r
vt.tempseg_usage,\r
vt.segtype\r
FROM gv$session vs,\r
(SELECT inst_id,\r
username,\r
session_addr,\r
segtype,\r
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage \r
FROM gv$tempseg_usage \r
GROUP BY inst_id, username, session_addr, segtype \r
ORDER BY 4 DESC) vt \r
WHERE vs.inst_id = vt.inst_id \r
AND vs.saddr = vt.session_addr \r
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;