Re: limit temp usage before error or out of space condition?

From: Yong Huang <>
Date: Wed, 23 Oct 2013 07:46:51 -0700 (PDT)
Message-ID: <>

We have this problem occasionally. Unfortunately I don't think Oracle has a way to limit one user or one session's usage of temp space so as to leave some space for others using the same temporary tablespace. You can have a script frequently checking v$tempseg_usage (or v$sort_usage) for blocks a session is using, and alert if it exceeds a threshold defined by you. Save the captured data in a table or file for later review, since you may have missed the critical moment because runaway SQLs came too fast. Once they're identified, assign the user(s) to a dedicated temporary tablespace. If the SQLs did run to completion (not failed), check v$sql_workarea for entries with high max_tempseg_size and last_tempseg_size and have the developers take a look.

Yong Huang

> Does anyone have any ideas how to limit temp usage before the query at
> fills up the temp tablespace and then suspends everyone else until
the condition
> is cleared? Is there a way to monitor queries that are
using too much temp and
> send an email? Maybe in OEM?

> I use resumable already. Just wanted to see if I could catch them before
they suspend.

Received on Wed Oct 23 2013 - 16:46:51 CEST

Original text of this message