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!

Answer 1

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.

Answer 2

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.