SQL占用TEMP
--16指的是blocksize
select distinct tu.username,machine,sid,tablespace,blocks*16/1024 "USE(MB)",segtype,tu.sql_id,sql_text
from
(select username,session_addr,sqladdr,sqlhash,sql_id,tablespace,sum(blocks) blocks,segtype,contents from v$tempseg_usage group by username,sqladdr,sqlhash,sql_id,tablespace,segtype,contents,session_addr) tu,
(select sql_text,sql_id,address,hash_value from v$sql group by sql_text,sql_id,address,hash_value) sq,
(select username,machine,saddr,sid from v$session) se
where tu.sqladdr=sq.address and tu.sql_id=sq.sql_id and tu.sqlhash=sq.hash_value and tu.username=se.username and tu.session_addr=se.saddr
order by "USE(MB)" desc;
select distinct tu.username,machine,sid,tablespace,blocks*16/1024 "USE(MB)",segtype,tu.sql_id,sql_text
from
(select username,session_addr,sqladdr,sqlhash,sql_id,tablespace,sum(blocks) blocks,segtype,contents from v$tempseg_usage group by username,sqladdr,sqlhash,sql_id,tablespace,segtype,contents,session_addr) tu,
(select sql_text,sql_id,address,hash_value from v$sql group by sql_text,sql_id,address,hash_value) sq,
(select username,machine,saddr,sid from v$session) se
where tu.sqladdr=sq.address and tu.sql_id=sq.sql_id and tu.sqlhash=sq.hash_value and tu.username=se.username and tu.session_addr=se.saddr
order by "USE(MB)" desc;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16546776/viewspace-747801/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
TEMP表空间使用率
下一篇：
没有了~
请登录后发表评论
登录
全部评论
