temp段使用情况

--当前临时段的使用情况，这个临时空间使用需要等到Session退出后才会释放，那么在并发很高的情况下，这一空间使用就将值得关注
SELECT s.username, s.SID, u.TABLESPACE, u.CONTENTS, u.segtype,
ROUND (u.blocks * 8192 / 1024 / 1024, 2) mb
FROM v$session s, v$tempseg_usage u
WHERE s.saddr = u.session_addr AND u.CONTENTS = 'TEMPORARY'
ORDER BY mb DESC;

---获取这些引发临时空间使用的SQL语句

SELECT /*+ rule */
DISTINCT a.SID, a.process, a.serial#,
TO_CHAR (a.logon_time, 'YYYYMMDD HH24:MI:SS') LOGON, a.osuser,
TABLESPACE, b.sql_text
FROM v$session a, v$sql b, v$sort_usage c
WHERE a.sql_address = b.address AND a.saddr = c.session_addr;

-------- 临时
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by MB DESC ;

---临时表空间的定义
select BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from dba_tablespaces where tablespace_name='TEMP';