Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespace temp is growing fast

Re: Tablespace temp is growing fast

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Sat, 02 Jun 2007 07:57:31 -0700
Message-ID: <1180796251.720101.209140@q66g2000hsg.googlegroups.com>


On Jun 2, 7:45 am, RC <roynos..._at_hotmail.com> wrote:
> Hi,
>
> At my customer there is a problem with the Oracle8i database. At
> certain moment we saw that tablespace temp was growing 1,3 Mb per
> second. It growed 3Gb per dag, while there were no applicatoins
> attached to the database.
> Is there a way to find out which objects in the database is causing
> this growth?
>
> Thank you in advance, Roy

Just run a query that identifies sessions which have allocated space from the temp tablespace. If you search around in cdos you will find several past examples of similar questions that include the sql needed.

It will only work and you have to catch the sessions while the usage is occurring. The good news is that it takes a while to use up and allocate significant amounts of temp space. Many places have put in scripts through a scheduler that run periodically ( every 10 minutes perhaps ), find out who is allocating large amounts of space, and perhaps might kill those sessions after they exceed some threshold.

In my experience sometimes this kind of behavior may occur from people trying to do analysis on an oltp type system that have included cartesian joins ( not enough join conditions between tables ). Received on Sat Jun 02 2007 - 09:57:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US