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
Registered: December 2007
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,
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
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.
Current Time: Wed Apr 26 09:15:47 CDT 2017
Total time taken to generate the page: 0.15640 seconds