*********************************************************************** * Description: SQL for querying temp tablespace utilization by session * Compatiablity: RDBMS 11g, 12c * Date: 04:08 PM EST, 04/01/2017 *********************************************************************** <1> Checking temp tablespace usage by session: -- V$SORT_USAGE can be replacable by view V$TEMPSEG_USAGE | |__ SQL> select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid, a.serial#, a.username, a.osuser, a.status from v$session a, v$sort_usage srt where a.saddr = srt.session_addr order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks; <2> Similiar query with more detail: | |__ SQL> SELECT b.TABLESPACE , a.paddr , a.saddr , b.segfile# , b.segblk# , ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.osuser , a.program , a.status FROM v$session a , v$sort_usage b , v$process c , v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE , b.segfile# , b.segblk# , b.blocks; TABLESPACE SEGFILE# SEGBLK# SIZE_MB SID SERIAL# USERNAME OSUSER PROGRAM --------------- ---------- ---------- ---------- ---------- ---------- --------------------- ------------------------------ ------------- TEMP 201 271232 1 364 20645 SYSTEM NT AUTHORITY\ANONYMOUS LOGON w3wp.exe TEMP 201 272640 1 364 20645 SYSTEM NT AUTHORITY\ANONYMOUS LOGON w3wp.exe TEMP_RPT 202 127104 16 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe TEMP_RPT 202 129152 16 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe TEMP_RPT 202 387200 16 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe TEMP_RPT 202 389248 16 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe TEMP_RPT 203 36992 64 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe TEMP_RPT 203 1138816 16 359 60693 RPTVIEW NT AUTHORITY\ANONYMOUS LOGON CrystalService.exe
Your Comments