Home » SQL & PL/SQL » SQL & PL/SQL » CLOB processing taking up large area in temp tablespace
CLOB processing taking up large area in temp tablespace [message #241891] Wed, 30 May 2007 19:16 Go to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
We are having a stored procedure, which is called from a Java process to enqeue a message in to AQ. The input for the procedure is CLOB.

In some installations, the temp tablespace is growing in large volumes and the only process accessing the temp tablespace is the procedure.

We are doing a trim on the clob variable inside the procedure. Will that cause any issues like this ?

The procedure is as below.
procedure enq_msg
(queue_name varchar2, msg_text in clob,p_result out number) as
queue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id          RAW(16);
v_msg_text			clob default msg_text;
v_queue_name		varchar2(40) deafult queue_name;
BEGIN
    v_msg_text := trim(v_msg_text);
    v_queue_name := queue_name;
    DBMS_AQ.ENQUEUE(queue_name => v_queue_name,
					enqueue_options => queue_options,
					message_properties => message_properties,
					payload => v_msg_text,
					msgid => message_id
				   );
	commit;
	p_result := 0;
exception
   when no_data_found then
     p_result := 1;
	 rollback;
   when others then
	 rollback;
     p_result := sqlcode;
END enq_msg;


Oracle version is
Oracle9i 9.2.0.5.0 Statndard edition

[Updated on: Thu, 31 May 2007 08:49]

Report message to a moderator

Re: CLOB processing taking up large area in temp tablespace [message #241922 is a reply to message #241891] Thu, 31 May 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and apply How to format your posts.

And give your Oracle version (4 decimals).

Regards
Michel
Re: CLOB processing taking up large area in temp tablespace [message #242048 is a reply to message #241891] Thu, 31 May 2007 08:50 Go to previous messageGo to next message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
updated the post
Re: CLOB processing taking up large area in temp tablespace [message #242080 is a reply to message #241891] Thu, 31 May 2007 09:41 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think the trim deallocates the memory allocated to your clob but can't affirm it.

Regards
Michel
Previous Topic: to get record in a single row
Next Topic: "ORA-01427: single-row subquery returns more than one row"
Goto Forum:
  


Current Time: Sun Dec 11 02:25:38 CST 2016

Total time taken to generate the page: 0.10696 seconds