当你想知道是哪条sql在占用temp表空间的时候，你可以这样：
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, 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;
我们都知道，9iR2/10gR2里除了v$tempseg_usage外，还有v$sort_usage。单从结构上来看，v$tempseg_usage和v$sort_usage没有任何区别，也就是说，上述sql其实是可以替换成：
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_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;
那v$tempseg_usage和v$sort_usage有什么不同吗？
答案是----在9iR2/10gR2里两者没有区别；在817里没有V$TEMPSEG_USAGE但是有V$SORT_USAGE。
你可以用dbms_metadata.get_ddl结合v$fixed_view_definition来跟一下上述两个视图的定义，你会发现，在9iR2/10gR2里两者确实是没有区别的，如下所示：
V$SORT_USAGE:
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, ktssotsn,
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'),
ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno
from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#;
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, ktssotsn,
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'),
ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno
from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#；
V$TEMPSEG_USAGE:
CREATE OR REPLACE PUBLIC SYNONYM "V$TEMPSEG_USAGE" FOR "SYS"."V_$SORT_USAGE"；
CREATE PUBLIC SYNONYM "V$TEMPSEG_USAGE" FOR "SYS"."V_$SORT_USAGE"
但是现在问题来了，没有区别，那oracle这里为什么要引入两个一模一样的视图？
817里没有V$TEMPSEG_USAGE这个视图，但是有V$SORT_USAGE。也就是说V$TEMPSEG_USAGE实际上是在V$SORT_USAGE之后引入的视图，是因为oracle觉得V$SORT_USAGE不能准确的反映这个视图的含义吗（sort要能在pga里排下就不用temp segment了）？
有一点可能需要我们注意----67534.1里指出：
V$SORT_USAGE shows the current users of any sort segment. It does NOT show TEMPORARY segments used for CTAS or similar operations for while a segment is being built but does show those used for SORT operations, whether in a PERMANENT or TEMPORARY tablespace.
也就是说当你使用CTAS创建表的时候，虽然用到了temp segment，但实际上V$TEMPSEG_USAGE和V$SORT_USAGE并不会有任何记录。
Session 1：
SQL> select count(*) from armshistemp.uplpdt;
COUNT(*)
----------
6962716
SQL> create table t4 as select * from armshistemp.uplpdt where rownum<1000001;
Table created
Session 2：
SQL> select count(*) from v$tempseg_usage;
COUNT(*)
----------
0
SQL> select count(*) from v$sort_usage;
COUNT(*)
----------
0
session 1里创建表t4的时候同时在session 2里查看v$tempseg_usage和v$sort_usage，可以从结果里看到，上述两个视图里确实没有记录。
好了，我们现在来让v$tempseg_usage和v$sort_usage产生记录：
Session 1：
SQL> create index idx_uplpdt_test on armshistemp.uplpdt(updbth);
Index created
在创建上述index的时候同时去查看v$tempseg_usage和v$sort_usage，如下所示：
Session 2：
SQL> select * from v$sort_usage;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
DRAS DRAS 07000000292CF190 847 070000002B68BDE8 2296808019 TEMP TEMPORARY SORT 202 127753 70 8960 2
SQL> select * from v$tempseg_usage;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
DRAS DRAS 07000000292CF190 847 070000002B68BDE8 2296808019 TEMP TEMPORARY SORT 202 127753 138 17664 2
这里的含义是一目了然的！
这两个视图显示的都是临时表空间中分配的segment,,包含sort/hash以及lob与临时表的segment,,create table 以及index产生的temporary segment不会在这里显示, 不过可以查看dba_segments看数据表空间中的temporary segment.