"Real Creator" of temporary segments ?

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 - 14:32:28 CDT