OraFAQ Forum: Performance Tuning » CLOB usage and issue with TEMP tablespace

Home » RDBMS Server » Performance Tuning » CLOB usage and issue with TEMP tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CLOB usage and issue with TEMP tablespace [message #289578]

Mon, 24 December 2007 02:48

susmita
Messages: 1
Registered: December 2007

Junior Member

Hello,

I am new to Oracle. I am facing a problem in Oracle 10g(Windows 2003 SP1) using CLOB.
My application connects to oracle database through multiple sessions and bulk inserts data into a table which is created as:

CREATE TABLE SEOSDATA(
ENTRYID NUMBER(20, 0) NOT NULL,
DOMAINNAME VARCHAR2(765) NOT NULL,
USERNAME VARCHAR2(765) NOT NULL,
EVENTTYPE NUMBER(10, 0) NOT NULL,
LOGNAME VARCHAR2(765) NOT NULL,
TIMSTAMP DATE NOT NULL,
SOURCE VARCHAR2(765) NOT NULL,
COMPUTERNAME VARCHAR2(765) NOT NULL,
EVENTID NUMBER(10, 0) NOT NULL,
EVENTCATEGORY VARCHAR2(765) NOT NULL,
SEARCHSTRINGS VARCHAR2(765),
MSGTEXT CLOB,
CONSTRAINT PK_SEOSDATA PRIMARY KEY (ENTRYID)
);

These insert queries are using TEMP tablespace and over a period of time the TEMP tablespace is getting filled up, resulting into 'ORA-1652:unable to extend temp segment by 128 in tablespace TEMP;'.
Increasing the size of the TEMP tablespace just delays the occurrence of the problem.

Is there a way I can disable TEMP tablespace usage by CLOB? How can I clear the contents of the TEMP tablespace while the application is running?

The TEMP tablespace is created as:

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'E:\oracle\product\10.2.0\oradata\oraenf\temp01.dbf' SIZE 256M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

The insert query is
INSERT INTO SEOSDATA
(ENTRYID,DOMAINNAME,USERNAME,EVENTTYPE,LOGNAME,TIMSTAMP,SOURCE,COMPUTERNAME,
EVENTID,EVENTCATEGORY,SEARCHSTRINGS,MSGTEXT)
VALUES (ENTRYID_SEQ.NEXTVAL,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)

The approx size of insert query around 1MB.

I have created this temp tablespace on my test machine just to reproduce the problem quickly. The tablespace on production machine is of 5GB.

Please let me know if more information is required.

Report message to a moderator

Re: CLOB usage and issue with TEMP tablespace [message #289586 is a reply to message #289578]

Mon, 24 December 2007 03:09

halfydba
Messages: 20
Registered: November 2007
Location: Australia

Junior Member

I gather you are using a anonymous pl/sql block to run this. Your temp lob data has to use temporary tablespace to store the information. This stops your physical memory from filling up.

Do you have the rest of your PL/SQL block?

Report message to a moderator

Previous Topic:	Long Running SQL- CLOB vs TO_CHAR
Next Topic:	EXECUTION TIME

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:01:00 CDT 2024