Query to find which user using temp tablespace

select username, tablespace ,sql_id from v$tempseg_usage;

(or)

select S.sid,S.username,U.tablespace,S.sql_hash_value ||’/’|| U.sqlhash hash_value,
U.segtype,u.contents,U.blocks  from v$session S, v$tempseg_usage U where
S.saddr=U.session_addr order by U.blocks;

(or)

select S.sid, S.username “DB_USER”, P.username “OS_USER” ,
P.spid,U.tablespace,S.sql_hash_value ||’/’|| U.sqlhash “HASH_VALUE “, U.segtype,U.contents,U.blocks
from v$process p , v$session S ,v$tempseg_usage U
where S.saddr=U.session_addr and p.addr=s.paddr
order by U.blocks;

  • January 21, 2019 | 17 views
  • Comments