What causes TEMP segment use

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Sun, 11 Apr 2004 13:56:10 -0400
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73075BD2E9@xchgbrsm1.corp.espn.pvt>

I have a process as outlined below ...
Loop

dequeue a message from a specified queue.
message is of type CLOB.
in another loop, use dbms_lob.read to read 1024 characters from the lob at a time.
using utl_raw and utl_tcp send each chunk across network.
loop back to the dbms_lob.read call until done. End loop;

Now, there are no following query shows number of extents used in v$sort_usage growing ... we send messages continuously few a minute.

SELECT s1.username, s1.sid, s1.serial#, s2.contents, s1.sql_address, s2.extents
FROM v$session s1, v$sort_usage s2
WHERE s1.saddr = s2.session_addr
order by 6 desc
/

The question is if I am not performing any queries in my code, why should my usage of TEMP be increasing?? Maybe I am missing something here, but can't put my finger on it. This process last week failed with ora-1652, so I am trying to investigate that as well.

This is all on a 9204 dual node RAC instance.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art !

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Sun Apr 11 2004 - 12:52:47 CDT