Lucene Indexing from Oracle:ORA-01652: unable to extend temp segment

Question

I met a problem when using Lucene to build full-text index of the data from the Oracle 11g database, with the following information: "ora-01652 unable to extend temp segment by 128 in tablespace temp, on MDSYS.SDO_RDF_TRIPLE_S", line 608"

The total size of the dataset is about 1.5GB. After the problem occur, I followed some instructions online:

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE 'D:\oracle\oradata\temp01.dbf' SIZE 2048M AUTOEXTEND ON MAXSIZE UNLIMITED;  

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

However, the problem is still there. The disk space is enough, though. Can anyone give me some help? Thanks in advance!

`in tablespace temp` <> `in tablespace temp01`. – Benoit Mar 06 '12 at 08:34 — Benoit, Mar 06 '12 at 08:34

score 2 · Accepted Answer · answered Mar 06 '12 at 00:13

2

What tablespace is MDSYS.SDO_RDF_TRIPLE_S in? That's the tablespace to which space needs to be added. Also, MAXSIZE UNLIMITED doesn't really mean unlimited; on most platforms, that means 32767 MB.

answered Mar 06 '12 at 00:13

Adam Musch

13,286
2
28
32

Thanks for your reply! I think this is the reason because every time when the program throws out this database exception(ORA-01652), the size of the "dbf" file is roughly 33GB. So what should I supposed to do if the table space needs more than 33GB space? – Chen Xie Mar 06 '12 at 04:34
Since the dataset itself is just 1.5GB, can anyone tell why the index building process by Lucene consumes that much of disk space? – Chen Xie Mar 06 '12 at 04:38
@ChenXie: `Alter tablespace some_tablespace add datafile some_path_to_file size 1023m autoextend on next 1024m maxsize unlimited` – Adam Musch Mar 06 '12 at 05:53

score 0 · Answer 2 · answered Mar 06 '12 at 08:02

Changing the default temporary tablespace for the database doesn't modify the assigned value for existing users who explicitly had a temporary tablespace set. Check the user you're connecting as, in dba_users, and if it has a different temporary tablespace do alter user <id> temporary tablespace temp01.

You could also have increased the size of the existing temporary tablespace, by increasing the size of its tempfile, setting that to autoextend, or adding an additional tempfile. However, if this is a one-off task then creating a new large tablespace for it and dropping it afterwards may not be a bad idea.

Lucene Indexing from Oracle:ORA-01652: unable to extend temp segment

2 Answers2