×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Jon Nickerson
Added: Apr 3, 2018 6:49 PM
Modified: Apr 3, 2018 6:51 PM
Views: 1
Tags: no tags
  1. -- http://www.dba-oracle.com/t_monitoring_temp_tablespace_usage.htm
  2. SELECT DISTINCT
  3.    c.username "user",
  4.    c.osuser,
  5.    c.sid,
  6.    c.serial#,
  7.    b.spid "unix_pid",
  8.    c.machine,
  9.    c.program "program",
  10.    a.blocks * e.block_size/1024/1024 mb_temp_used  ,
  11.    a.tablespace,
  12.    d.sql_text
  13. FROM
  14.    v$sort_usage a,
  15.    v$process b,
  16.    v$session c,
  17.    v$sqlarea d,
  18.    dba_tablespaces e
  19. WHERE
  20.    c.saddr=a.session_addr
  21. AND
  22.    b.addr=c.paddr
  23. AND
  24.    c.sql_address=d.address(+)
  25. AND
  26.    a.tablespace = e.tablespace_name;
  27.