Could not rebuild indexes for dataset - Operation Failed [ORA-29858: Fehler bei Ausführung der Routine ODCIINDEXALTER

[JamesMorrison1]

Hello Everyone

Just trying to rebuild some indexes using the Rebuild Indexes Geoprocessing Tool in ArcGIS Pro in our new upgraded oracle and geodatabase Test environment passing in an sde connection file:

Geodatabase 11.1
ArcGIS Pro 3.1.0
Oracle 19.16.0.0
ST_GEOMETRY
Linux

Error message in ArcGIS Pro

Could not rebuild indexes for dataset DATA.DATA_LI.[
Operation Failed [ORA-29858: Fehler bei Ausführung der Routine ODCIINDEXALTER
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP2 erweitert werden
ORA-06512: in "SYS.DBMS_SQL", Zeile 1134
ORA-06512: in "SDE.ST_DOMAIN_METHODS", Zeile 1688
ORA-06512: in "SDE.ST_DOMAIN_METHODS", Zeile 2487]
Operation Failed [ORA-29858: Fehler bei Ausführung der Routine ODCIINDEXALTER
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP2 erweitert werden
ORA-06512: in "SYS.DBMS_SQL", Zeile 1134
ORA-06512: in "SDE.ST_DOMAIN_METHODS", Zeile 1688
ORA-06512: in "SDE.ST_DOMAIN_METHODS", Zeile 2487]]

From the error message I assume the TEMP2 tablespace is too small: ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP2 erweitert werden

It is currently set at: temp2.dbf is size 512m;

If it is too small - Does the rebuild operation in Oracle really use the temporary tablespace and why?

Thank you for the clarification

[MarceloMarques]

Temp 512mb is too small, set it to a large value 2gb or more, Oracle needs temp space to perform operations like sorting, etc., even during certain operations like rebuild index the Oracle database will need temp space.

[JamesMorrison1]

Thanks for your replay Marcelo - always appreciated.

I was a little confused. The feature class where I rebuild the index is a Schema called DATA1 and it has Tablespace called DATA (I'm making these names up for demonstration). And my old Oracle guide askTom (Tom Kyte) says....“Oracle will put the newly rebuilt index structure into a TEMP segment in the tablespace where the index is going to go.” 

I assumed this would be DATA and not TEMP2? Or am I missing the point here?

[MarceloMarques · Accepted solution]

Yes the temporary index goes to the data tablespace but Oracle database still needs plenty of empty space in the Temp tablespace to perform other operations like sorting, etc.

[JamesMorrison1]

Thank you Marcelo always great feedback