How Do You Find Who And What SQL Is Using Temp Segments [ID 317441.1] | |||||
| |||||
修改时间 13-APR-2010 类型 HOWTO 状态 PUBLISHED |
In this Document
Goal
Solution
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.
Goal
How Do You Find Who And What SQL Is Using Temp Segments?Solution
For 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-665661/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7199859/viewspace-665661/