oracle查找什么sql语句占用了临时段

[table=98%]
[td=4,1]How Do You Find Who And What SQL Is Using Temp Segments [ID 317441.1][td=2,1]
[td=6,1]
[td=2,1,25%][td=3,1,50%][i]修改时间 13-APR-2010 [i]类型 HOWTO [i]状态 PUBLISHED

In this Document
[size=-1]https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=317441.1#GOAL]Goal
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=317441.1#FIX]Solution
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=317441.1#REF]References

Applies to: Oracle Server – Enterprise Edition – Version: 10.1.0.2 and later [Release: 10.1 and later ]
Information in this document applies to any platform.

GoalHow Do You Find Who And What SQL Is Using Temp Segments?

SolutionFor 8.1.7 to 9.2:

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;

For 10.1 and above:

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;

NOTE: Indications are that the SQL retrieval does not work for parallel query slaves … only the parent process