Re: "Real Creator" of temporary segments ?

There is a script on my website which identifies the owning session if it is still alive. I did not write it so I don't know how it will behave if the session has died.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Silvio Seifert wrote in message <7ipfcg$6su$1_at_news01.btx.dtag.de>...
>We run Oracle 7.3.2.3. The tablespace TEMP is about 500MB in size and
>set to TEMPORARY. All of the database users have the this tablespace
>assigned as their temporary tablespace through the CREATE USER
>statement.
>
>In the past, one of the processes running against the database did
>some action and filled up the TEMP tablespace, creating one big
>temporary segment of about the same size as the TEMP tablespace. We
>don't know WHICH process and WHAT it was executing. The action
>probably failed, Oracle logged into the alert logfile an ORA-error
>"Can't extend temp segment by XXX in tablespace TEMP" because of lack
>of available space in the TEMP tablespace for further growing of the
>temporary segment.
>
>SMON was not be able to clean up the temporary segment. Only after a
>restart of the database instance we could get rid off this temporary
>segment.
>
>Because of the fact, that the owner of temporary segments created
>during execution of certain statements is the database user SYS, here
>my question.
>
>Is there any way, knowing all the information from DBA_SEGMENTS about
>the temporary segment in question, to find out the database user (not
>SYS !!!), whose action caused the creation of the temporary segment ?
>Furthermore, is it possible to find out the action too ?
>
>Thanks in advance
>
>Silvio Seifert
>Foehrer Strasse 34
>65199 Wiesbaden
>Germany
Received on Sat May 29 1999 - 15:47:51 CDT