Home » RDBMS Server » Performance Tuning » CLOB usage and issue with TEMP tablespace
CLOB usage and issue with TEMP tablespace [message #289578] Mon, 24 December 2007 02:48 Go to next message
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.
Re: CLOB usage and issue with TEMP tablespace [message #289586 is a reply to message #289578] Mon, 24 December 2007 03:09 Go to previous message
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?
Previous Topic: Long Running SQL- CLOB vs TO_CHAR
Next Topic: EXECUTION TIME
Goto Forum:
  


Current Time: Sat Dec 10 06:42:39 CST 2016

Total time taken to generate the page: 0.04124 seconds