Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Trying to catch who/what spike TEMP tablespace to 99.9%
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
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
and a.address (+) = s.sql_address and a.hash_value (+) = s.sql_hash_value and p.name = 'db_block_size'
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