Taking TEMP table space one step furthur -to trigger the script

[Ratzie]

Thank you for the great replys now I want to take it one step further. 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.

[Steve Steel · Accepted solution]

Hi

Not sue you can but look at

http://docs.rinet.ru/O8-3w/ch07/ch07.htm

Monitoring the Tablespaces
As described previously, you can use Storage Manager to view both the tablespaces and the datafiles for space utilization. The information provided by Storage Manager is quite useful. From the Tablespace view, you can see both the space allocated to the tablespace and the amount of space used in the tablespace (refer to Figure 7.6). The SYSTEM tablespace has used approximately 11.7MB of its available 25MB. The colored bar shows this to be about 45% used.

This feature is also available from the Datafiles option (refer to Figure 7.12). This shows approximately the same view as in the Tablespace view since both tablespaces, SYSTEM and DOGS, have one associated datafile. In situations where more datafiles exist, you might be able to determine whether balancing of space is an issue. You must use the NT Performance Monitor to determine whether I/O balancing is a problem.



Steve Steel

[Ratzie]

Thanks