临时表空间使用情况

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.BYTES) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

使用临时表空间的进程或者sql

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks,b.SEGTYPE, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address(+)= a.sql_address

AND c.hash_value(+)= a.sql_hash_value

ORDER BY b.TABLESPACE,b.blocks desc;

10G 以后也可以使用