Trying to catch who/what spike TEMP tablespace to 99.9%

From: LHradowy <laura.hradowy_at_NOSPAM.mts.caaaaa>
Date: Fri, 19 Nov 2004 09:05:30 -0600
Message-ID: <2Znnd.10046$wj7.46240@news1.mts.net>

  I need to be able to gather some stats as to who and what has spiked the tablespace to 99%. If it is an application issue then I have proof to go back to the vendor.

I have found a sweet script that will let me know what user is using what process and how much tablespace... I think...

Now here is the catch, how do I incorporate this to run when I reach a certain tablespace spike, like lets say 80%. I do not know where to start...

Here is the script that I found on lazydba

• tempspace_users.sql break on report compute sum of mb on report compute sum of pct on report

col sid format a10 heading "Session ID"
col username format a10 heading "User Name" col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name" col size_mb format 999,999,990.00 heading "Mbytes|Used"

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text, round(((u.blocks*p.value)/1024/1024),2) size_mb from v$sort_usage u,

v$session s,
v$sqlarea a,
v$parameter p

where s.saddr = u.session_addr

and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'

group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)), u.tablespace,
round(((u.blocks*p.value)/1024/1024),2)
/
quit

I am hoping I can catch the right user and process that almost max'ed out my TEMP tablespace. Received on Fri Nov 19 2004 - 09:05:30 CST